THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

Michael Zilberstein

Restoring master database

Disclaimer: I'm not an infrastructure DBA.Probably everything below is just bullshit.

Recently our talented IT guys destroyed disk on a virtual machine. They chose the disk carefully - it was the one with master database. How do you restore master db from backup? Start SQL Server service from command line in single user mode and execute RESTORE DATABASE command. OK, start in single user mode fails because it doesn't find master database files. At this point I would like to take backup file and instead of restoring database in working state, just extract master.mdf and mastlog.ldf files (not just them, see ahead). But unfortunately it is unsupported (or I failed to find the right option - see disclaimer). Next step - find SQL Server installation with the same product version on another server, stop the service, copy master.mdf and mastlog.ldf (can't be done without stopping service - files are inaccessible). Again "sqlserver.exe -m"... This time mssqlsystemresource.mdf and mssqlsystemresource.ldf are missing. Copy them too. Also model.mdf and modellog.ldf. Again "sqlserver.exe -m". This time I faced real problem - startup failed on creating tempdb because on the server I took master.mdf from, tempdb datafile sat on disk F and tempdb log on disk H. While on my server I have only C and D. As far as I understand, at this point I have 3 options: re-install SQL Server and afterwards restore all databases including master from backup, add disks F and H or find installation where tempdb sits on C or D. Luckily I could just add F and H and it solved the problem. But all the sequence of actions seems unreasonable. BTW, after starting service in single user mode, I restored master from backup and it automatically moved tempdb to its old location on D - master contains path to all system databases. Afterwards F and H have been removed. Seems to me totally unreasonable that in order to restore master database new logical disks had to be created. I see 2 possible solutions: to be able to extract mdf and ldf files from backup without actually restoring database or to be able to configure path to system databases externally in service properties like path to master and errorlog.

Published Tuesday, December 30, 2008 8:03 PM by Michael Zilberstein
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Leo Pasta said:

I believe the recommended method is described here: http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

December 30, 2008 12:53 PM
 

Michael Zilberstein said:

Leo,

I wanted to restore master from backup, not to rebuild in order to keep all users, definitions etc. Restore for SQL2005 is described here:

http://msdn.microsoft.com/en-us/library/ms190679(SQL.90).aspx

And it starts from:

1. Start the server instance in single-user mode.

December 30, 2008 2:01 PM
 

Mike Walsh said:

Another good idea of a drill to add to recovery/restoration drills :)

Funny timing.. I was just giving someone a lecture about troubleshooting skills and trying to be proactive but I can't honestly tell you when the last time I practiced playing a "oops master is gone and I want it back as it was" drill...

I believe the only time I had to do this, I rebuilt master and then restored the backup of master over the newly rebuilt master and that did the trick.

Maybe this is one good piece of doing Volume Shadow Service backups as some companies are moving towards. Takes a snapshot of the actual .mdf and .ldf files. Scares the crap out of me but it works :)

December 30, 2008 11:25 PM
 

Uri Dimant said:

Michael,shalom

Why not scripting out all users/logins along with jobs and dts/ssis and not to mess with those problems but just re-install SQL Server and apply all needed scipts?

December 31, 2008 3:14 AM
 

Michael Zilberstein said:

Uri, shalom!

First issue - if I have only backup while master itself is gone, how can I script anything? Do you mean, instead (or in addition to) backing up master, also script everything?

Second issue - after re-installing server, the easiest way to restore everything is just to start service in single-user mode and execute

RESTORE DATABASE master FROM DISK = '[path to backup file]' WITH REPLACE;

No need to run any further scripts. It'll also restore tempdb settings (instead of configuring everything again including adding files - I use to have file per core and also place them on different physical disks if possible).

December 31, 2008 4:00 AM
 

Michael Zilberstein said:

Mike,

Do you mean that some tools now are capable of taking hot snapshot of .mdf  and .ldf file that is consistent? Never heard of it but sounds very interesting - add on-the-fly compression and you have excellent backup tool that will compete with LiteSpeed and other 3rd party tools.

December 31, 2008 4:05 AM
 

Saggi Neumann said:

Hey Michael,

I didn't get it - why not rebuild master and then restore?

December 31, 2008 4:15 AM
 

Michael Zilberstein said:

Hi Saggi,

Probably that was the right thing to do but I'm a confused with versions. When master is being rebuilt from the installation media, it rebuilds Resource db (mssqlsystemresource) to the RTM version - without service packs, cumulative updates etc. What happens if afterwards master is restored from backup? Can it be done if backup is from, say, SP3 version or should I rebuild, run SP3 installation and only after that restore?

December 31, 2008 5:21 AM
 

Saggi Neumann said:

Well, since I don't remember the last time I rebuilt master (I think it was a 2000 version anyway), I'm not sure what's to be done with all the fixes, but I suppose that I'd follow the steps you mentioned:

1. rebuild master

2. install latest fixes

3. restore original master, msdb...

December 31, 2008 9:48 AM
 

Michael Zilberstein said:

Hi Saggi,

The way to rebuild master is the same in 2000 and 2005. I think, it was changed in 2008. Anyway, I don't rebuild master for lunch either :-). Taking into account (2), I think, there should be more clear way that won't require to re-install service pack and patches. Practically, the way I did it was faster, while your way is the most straightforward - can be recommended as best practice.

January 1, 2009 2:32 AM
 

Saggi Neumann said:

If I remember correctly, in 2000 it was done with the utility rebuildm and in 2005 it can only be done with sql server setup.

You were lucky to have another server installed with the same version so you could recover faster.

What we should try to do (if your proposal to make master recovery easier doesn't make it) is to make a copy (i.e. file copy, not backup...) of master, msdb, model and mssqlsystemresource every time you install a hotfix/CU/SP, so if these databases just disappear one day, you can copy and then restore over them...

January 1, 2009 12:11 PM
 

Harry Parkinson said:

Michael,

In your procedure you can avoid recreating the file paths and files for model, tempdb etc by starting MSSQL in single user mode with trace flag 3608. This instructs MSSQL to only recover master only during startup.

sqlservr.exe -s instance -c -m -f /t3608

Once started you can restore master from backup

January 2, 2009 5:54 AM
 

Chris Wood said:

Michael,

I had a similar experience with SQL2005 about 2 years ago. We take a flat file backup of all the system databases whenever we upgrade the build of SQL as our daily backups of the server do not look at open files. By having the flat file available we can copy it to the correct folder and bring up SQL2005 so we can recover to present. In this case we were expanding the disk array that held the system databases by taking a full backup to tape, with SQL Services shut down, and then replacing the disk array and copying the files back to the new disk. Somehow the system ldf (log) files did not get backed up and so when SQL tried to come up it couldn't. I could not find a way to get SQL started so I uninstalled and then re-installed. I got it back to the build that it had been then I tried to restore master from its last backup to disk. We take at least one system backup every day. It failed and said something like it was not a backup of the newly installed master. I stopped at that point and rested before so I had a clear mind to think things over. Later I just added back all the databases and logins and jobs etc from application database backups and manual keying.

This made me feel that each installation of SQL2005 had some unique identifier and that stopped my restore.

Is trace flag 3608 for SQL2008 only? I see it in 2008 BOL but not 2005 BOL.

Chris

January 2, 2009 4:44 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement