解决sqlserver 2008系统数据库msdb “可疑”的方法

《解决sqlserver 2008系统数据库msdb “可疑”的方法》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、解决SQLServer2008msdb可疑的数据库的方法请参考文章http://www.mssqltips.com/sqlservertip/2571/restoring-sql-server-system-databases-msdb-and-model/ProblemDuetoarecentrebuildofthemasterdatabaseforaSQLServerinstance,Inowneedtorestorethemsdbandmodeldatabases. Inthistipwewalkthroughthe

2、processthatyouneedtofollowtorestorethemodelandmsdbdatabasessuccessfully.SolutionBeforewegetstarted,let'squicklydiscusswhatthemsdbandmodeldatabasesareusedfor.·Themsdbdatabasecontainsscheduledjobs,alertsandbackuphistorythereforeaproperbackupplanshouldbeimplementedfo

3、rthemsdbsystemdatabase.·ThemodeldatabaseistheblueprintforcreatinganynewuserdatabaseforthatparticularinstanceofSQLServer.DBAscanmodifythemodeldatabasewithwhateversettingsthatarerequiredandwhenanewuserdatabaseiscreateditwillreflecttheconfigurationofthemodeldatabase.

4、Ifthemodeldatabasehasbeenmodifiedthenitshouldbebackedup.Henceboththemsdbandmodeldatabasesmayneedtoberecoveredinscenarioslikedatabasecorruption,arebuildofthemasterdatabaseorafteranewserverconfiguration.Therestoreprocessofmsdbormodeldemandsadditionalconsiderationsth

5、anthatofauserdatabases. Therestoreprocessmaygetcomplicatedifversionsarenottrackedandexclusiveaccessisnotensuredinallaspects.Inthenextsectionswewillgothroughtherestoreprocessforthemsdbandmodeldatabases.Note:thisexercisewasperformedonSQLServer2005,butthesameruleswou

6、ldalsobeappliedonSQLServer2005andonwards.BackupmsdbUsethefollowingcommandtocreateafullbackupofthemsdbdatabaseusingT-SQLcommands.Youwillneedtomodifythescripttouseavalidbackuppath. Youcanperformthesameprocessforthemodeldatabaseaswell.--Script1:CreatebackupofmsdbUSE[

7、master]GOBACKUPDATABASE[msdb]TODISK=N'E:MSDB_Backup.Bak'WITHINIT,NAME=N'msdbBackupforMSSQLTipsDemo'GOAfterthisiscompletewehaveafullbackupthatcanberestored.PrepareforrestoreToensurewehaveasmoothrestoreweneedtofollowthesesteps.·Gettheversionofdestinationserver·Gett

8、heversionofsourceserveronwhichthebackupwascreated·Matchtheversionsforthesourceanddestinationservers·EnsureexclusiveaccesstothedatabaseThetermsourceserve