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

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

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

2、dtofollowtorestorethemodelandmsdbdatabasessuccessfully.SolutionBeforewegetstarted,let'squicklydiscusswhatthemsdbandmodeldatabasesareusedfor.·Themsdbdatabasecontainsscheduledjobs,alertsandbackuphistorythereforeaproperbackupplanshouldbeimplementedforthemsdbsystemdatabase.·Themodelda

3、tabaseistheblueprintforcreatinganynewuserdatabaseforthatparticularinstanceofSQLServer.DBAscanmodifythemodeldatabasewithwhateversettingsthatarerequiredandwhenanewuserdatabaseiscreateditwillreflecttheconfigurationofthemodeldatabase.Ifthemodeldatabasehasbeenmodifiedthenitshouldbeback

4、edup.Henceboththemsdbandmodeldatabasesmayneedtoberecoveredinscenarioslikedatabasecorruption,arebuildofthemasterdatabaseorafteranewserverconfiguration.Therestoreprocessofmsdbormodeldemandsadditionalconsiderationsthanthatofauserdatabases. Therestoreprocessmaygetcomplicatedifversions

5、arenottrackedandexclusiveaccessisnotensuredinallaspects.Inthenextsectionswewillgothroughtherestoreprocessforthemsdbandmodeldatabases.Note:thisexercisewasperformedonSQLServer2005,butthesameruleswouldalsobeappliedonSQLServer2005andonwards.BackupmsdbUsethefollowingcommandtocreateaful

6、lbackupofthemsdbdatabaseusingT-SQLcommands.Youwillneedtomodifythescripttouseavalidbackuppath. Youcanperformthesameprocessforthemodeldatabaseaswell.--Script1:CreatebackupofmsdbUSE[master]GOBACKUPDATABASE[msdb]TODISK=N'E:MSDB_Backup.Bak'WITHINIT,NAME=N'msdbBackupforMSSQLTipsDemo'GO

7、Afterthisiscompletewehaveafullbackupthatcanberestored.PrepareforrestoreToensurewehaveasmoothrestoreweneedtofollowthesesteps.·Gettheversionofdestinationserver·Gettheversionofsourceserveronwhichthebackupwascreated·Matchtheversionsforthesourceanddestinationservers·Ensureexclusiveacce

8、sstothedatabaseThetermsourceserve