Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

System Database restores vs rebuild Expand / Collapse
Author
Message
Posted Friday, April 25, 2014 9:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 11:25 AM
Points: 36, Visits: 121
Hello all,
I need to better understand under what circumstances you would rebuild versus restore the system databases? In a meeting about our backup policy, I brought up a "what if" scenario were the sql server would not start and we had to rebuild a new server. I was told, all I had to do in that case was restore the master db - no problem. However, if the sql won't start, there is no restoring the master database, it must be rebuilt, is that correct? That is what I've found so far in Microsoft's documentation anyway...

Thank you,
Karen

Post #1565138
Posted Friday, April 25, 2014 9:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 339, Visits: 3,307
I don't think I could put it any better than this. So I won't try. http://www.sqlskills.com/blogs/paul/disaster-recovery-101-restore-master-rebuild-master/

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1565145
Posted Friday, April 25, 2014 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 11:25 AM
Points: 36, Visits: 121
Thank you very much, looking at the link you supplied now.

I do have another question though :) our networking team do the backups using a third party software but what I get from them is always the mdf & ldf. ( I do my own backups, but lets pretend I don't )

If I am able to restore the master database because the sql server will start, I currently see I can type this into sqlcmd...

RESTORE DATABASE [master]
FROM DISK = 'D:\backup\master_backup.bak'
WITH CHECKSUM, STATS;

Does the restore command above only use .bak or can I specify an mdf & ldf?

Thank you,
Karen
Post #1565152
Posted Friday, April 25, 2014 9:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:44 AM
Points: 5,339, Visits: 9,795
Karen

If you have a backup file (.bak), you can restore the database. If you have the database files (.mdf and .ldf), you can attach the database. The procedure for doing both those operations is different for master from other databases.

I would advise against using the database files as a recovery strategy. If they were backed up using an open file agent while SQL Server was running, you've no guarantee of consistency and you could find yourself with corruption in your database. You're the DBA: insist on doing native backups yourself and ask the infrastructure people to back up the resulting backup files to disk.

John
Post #1565159
Posted Friday, April 25, 2014 11:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 11:25 AM
Points: 36, Visits: 121
Thanks for the reply John, but question....

I understand what you are saying about the consistancy issue being possible... but say I had a corrupt master occur on a production system, could I not just shut down the sql service, copy over a previous mdf and ldf for the master, start the service and be on my way? What would be an intelligent arguement to that? What are some of the obvious reasons not to do that?

I have attached/detached db's before, not system db's though so unsure how that process differs and haven't found info on it yet.

Thank you,
Karen

Post #1565187
Posted Friday, April 25, 2014 11:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:34 PM
Points: 23,343, Visits: 32,079
karend211 34657 (4/25/2014)
Thanks for the reply John, but question....

I understand what you are saying about the consistancy issue being possible... but say I had a corrupt master occur on a production system, could I not just shut down the sql service, copy over a previous mdf and ldf for the master, start the service and be on my way? What would be an intelligent arguement to that? What are some of the obvious reasons not to do that?

I have attached/detached db's before, not system db's though so unsure how that process differs and haven't found info on it yet.

Thank you,
Karen



Problem is that you can't be sure that previous mdf/ldf files will be in consistent state when you attempt to restart SQL Server with those files. That is the problem with relying on open file backups of the mdf/ldf database files. You really need to have SQL Server backups, either native or taken with a third party backup software such as Redgate Backup.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1565213
Posted Friday, April 25, 2014 1:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
http://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1565234
Posted Saturday, April 26, 2014 12:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:50 PM
Points: 6,319, Visits: 13,625
karend211 34657 (4/25/2014)
I need to better understand under what circumstances you would rebuild versus restore the system databases?

If you wanted to change the system collation.



karend211 34657 (4/25/2014)
I brought up a "what if" scenario were the sql server would not start and we had to rebuild a new server. I was told, all I had to do in that case was restore the master db - no problem.

Why would you need a new server just because sql server won't start?? What you'd do is check the event logs to see why sql server won't start and work from there.
If your master database was indeed corrupted you would restore it from backups, most backup policies take a copy of the raw files and use them as a backup base. You could even restore the backup of master to a different instance as a user database then detach it. Rename the disk files to master.mdf and mastlog.ldf and plug them into the broken instance then start the service.


karend211 34657 (4/25/2014)
However, if the sql won't start, there is no restoring the master database, it must be rebuilt, is that correct? That is what I've found so far in Microsoft's documentation anyway...

Thank you,
Karen


There are many reasons why the SQL Server service may fail to start, most common is a missing folder or permissions.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1565353
Posted Monday, April 28, 2014 1:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 11:25 AM
Points: 36, Visits: 121
Why would you need a new server just because sql server won't start??


I am thinking worst case scenario & want to be prepared & have a plan for all avenues. Yes, I'd check the windows event log & sql server error log but honestly, unless there is something in there that gives me something to go on, I would be tempted to not waste too much time. - have I mentioned I'm an "accidental" dba -- .net developer 85% of the time.

Thank you everyone for the replies & links posted, I've found them very informative. I think what i've got from this is to practice practice practice the different methods that may be needed for recovery.
Post #1565691
Posted Monday, April 28, 2014 2:26 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 339, Visits: 3,307
Bear in mind, the server you restore to should be exactly the same (version, sp possibly even cu, but I'd not swear to that one) as your old one otherwise it is unlikely to work.

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1565706
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse