April 5, 2012 at 12:25 pm
If master db gets corrupted, do I have to restore both system databases and user databases or is restoring master database sufficient?
April 5, 2012 at 12:27 pm
Restoring the master database is all you should have to do. Hopefully you back it up after all major changes, such as adding users, new databases, or removing users, old databases, etc.
April 5, 2012 at 12:56 pm
Lynn Pettis (4/5/2012)
Restoring the master database is all you should have to do. Hopefully you back it up after all major changes, such as adding users, new databases, or removing users, old databases, etc.
I got little confused after reading this article/blog:
http://www.sqlservercentral.com/articles/Administration/73207/
In this article, the owner mentions:
Fixed the corrupted Master database using the new command of SQL Server 2008 (provided graciously by Brian).
Restored the latest backup of the master database.
Restored the latest backups of all user databases.
April 5, 2012 at 1:30 pm
Except he doesn't mention if there were other issues that might have forced him to restore the user dbs or if he was just being proactive.
Have backups of the user dbs ready, but restore master only. Then run the DBCC commands against your other databases to make sure they are okay. If they are, I wouldn't worry about restoring the user databases, but I would take additional backups of everything so you have "before" and "after" versions available.
April 5, 2012 at 1:31 pm
If you restore a backup of your master database you won't need to restore user databases. The restored master will still have the user databases cataloged.
It's when you rebuild master from scratch that you need to either restore or attach (usually attach) the user databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2012 at 1:54 pm
GilaMonster (4/5/2012)
If you restore a backup of your master database you won't need to restore user databases. The restored master will still have the user databases cataloged.It's when you rebuild master from scratch that you need to either restore or attach (usually attach) the user databases.
Gail beat me to it.
Having done this recently - I can confirm this is the behavior. User databases do not need to be restored if you just restore the master database. The exceptions to this are 1) if you decide to restore to a version of master that was prior to the user databases having been created, and 2) if you rebuild from scratch like Gail said.
And if you are wondering - yes I did have to recover from scenario 1 since I had some sys admins stop SQL server services and then delete the system databases. Then the sys admin restored master to the RTM version. But I take backups of the master database and I was able to recover with a current backup of master.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2012 at 1:57 pm
SQLRNNR (4/5/2012)
GilaMonster (4/5/2012)
If you restore a backup of your master database you won't need to restore user databases. The restored master will still have the user databases cataloged.It's when you rebuild master from scratch that you need to either restore or attach (usually attach) the user databases.
Gail beat me to it.
Having done this recently - I can confirm this is the behavior. User databases do not need to be restored if you just restore the master database. The exceptions to this are 1) if you decide to restore to a version of master that was prior to the user databases having been created, and 2) if you rebuild from scratch like Gail said.
And if you are wondering - yes I did have to recover from scenario 1 since I had some sys admins stop SQL server services and then delete the system databases. Then the sys admin restored master to the RTM version. But I take backups of the master database and I was able to recover with a current backup of master.
And what kind of punishment befell the sys admin?
April 5, 2012 at 2:00 pm
Lynn Pettis (4/5/2012)
SQLRNNR (4/5/2012)
GilaMonster (4/5/2012)
If you restore a backup of your master database you won't need to restore user databases. The restored master will still have the user databases cataloged.It's when you rebuild master from scratch that you need to either restore or attach (usually attach) the user databases.
Gail beat me to it.
Having done this recently - I can confirm this is the behavior. User databases do not need to be restored if you just restore the master database. The exceptions to this are 1) if you decide to restore to a version of master that was prior to the user databases having been created, and 2) if you rebuild from scratch like Gail said.
And if you are wondering - yes I did have to recover from scenario 1 since I had some sys admins stop SQL server services and then delete the system databases. Then the sys admin restored master to the RTM version. But I take backups of the master database and I was able to recover with a current backup of master.
And what kind of punishment befell the sys admin?
If it had been my choice it would have been very severe. Otherwise just a slap on the wrist.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply