March 13, 2009 at 3:13 pm
[p]Our IBM Tivoli server backups do not pick up the system database files, so after recovering a server, there is no master.MDF, so I cannot start SQL Server (in any mode). I have on-disk backups of the system databases, but cannot restore these until SQL Server is running.
[/p][p]
The setup REBUILD option does not always work because you need to re-apply all updates (SP2 requires fixes to be removed first, but sometimes a fixe cannot be removed - the removal fails. [/p][p]
To cope with this, I am shutting down SQL Server and creating copies of the system database MDF and LDF files. As the number of servers grow, this gets to be a difficult thing to do because the copies need to be replaced every time we patch SQL Server (to ensure they are compatible).
[/p][p]
If there a better way of handling this situation? What would YOU do?
[/p]
March 13, 2009 at 3:42 pm
In a disaster recovery situation, we re-install SQL Server back to the appropriate version (all service packs/cumulative updates). Once installed, we then restore the databases from our backups up to the point in time of the disaster.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2009 at 4:00 pm
larry Hennig (3/13/2009)
[p]Our IBM Tivoli server backups do not pick up the system database files, so after recovering a server, there is no master.MDF, so I cannot start SQL Server (in any mode). I have on-disk backups of the system databases, but cannot restore these until SQL Server is running.[/p]
That scary that system databases are not being backed up. If agent can't backup system databases I would backup databases using SQL Server Agent and then put the backup files on tape.
[p]
The setup REBUILD option does not always work because you need to re-apply all updates (SP2 requires fixes to be removed first, but sometimes a fixe cannot be removed - the removal fails. [/p][p]
To cope with this, I am shutting down SQL Server and creating copies of the system database MDF and LDF files. As the number of servers grow, this gets to be a difficult thing to do because the copies need to be replaced every time we patch SQL Server (to ensure they are compatible).
[/p]
If you have full backup of system dbs it makes it easier to restore stuff. I keep a sharepoint sheet of all servers and their current version. So if server crashes I can rebuild it with the latest patch it is in. After then I can restore system databases in single user mode from backups. If you need to know what patches to install here is a great blog for that: http://sqlserverbuilds.blogspot.com/.
If you don't know the version but have bak files of system db you can use following command to find the system db version:
RESTORE HEADERONLY FROM ..... (BOL: http://msdn.microsoft.com/en-us/library/ms178536.aspx).
[p]
If there a better way of handling this situation? What would YOU do?
[/p]
Regular backup, verification, and lots of documentation (C.Y.A.) :-D.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 13, 2009 at 4:18 pm
[p]Thanks for the quick feedback. especially the tip about using RESTORE HEADERONLY on the master database!
[/p][p]
I do backup the system databases and recovery them as part of the SQL server recovery. The problem I desribed is that you cannot recover them unless SQL Server is running and you need at least master.MDF & master.LDF to start SQL Server in minimal mode.
[/p][p]
Re-installing SQL Server seems like a lot of work, especially if you are working to meet a 24-hour DR recovery time objective for a number of critical servers. For what it's worth, having a copy of those files has really shortened our DR recovery time because we do not have to re-install SQL Server.
[/p]
March 13, 2009 at 4:24 pm
Restore master
http://qa.sqlservercentral.com/Forums/FindPost490956.aspx
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 13, 2009 at 4:31 pm
making a copy of the system database files is something I do every time I Patch an instance and is the best workaraound for the situation you describe. doesn't seem like much work to me to do this.
Interested in your comments about rebuild, why should you have to remove some fixes? Have not had to do this for ages in 2005, so why is rebuild not rolling you forward to the correct version no. to enable the master restore?
This has never been a problem in rebuildm for SQL20000 and earlier.
---------------------------------------------------------------------
March 13, 2009 at 4:35 pm
larry Hennig (3/13/2009)
[p]Thanks for the quick feedback. especially the tip about using RESTORE HEADERONLY on the master database![/p][p]
I do backup the system databases and recovery them as part of the SQL server recovery. The problem I desribed is that you cannot recover them unless SQL Server is running and you need at least master.MDF & master.LDF to start SQL Server in minimal mode.
[/p][p]
Re-installing SQL Server seems like a lot of work, especially if you are working to meet a 24-hour DR recovery time objective for a number of critical servers. For what it's worth, having a copy of those files has really shortened our DR recovery time because we do not have to re-install SQL Server.
[/p]
Yes, you can shorten your recovery time by a couple of hours, except you can't get the system back up and running because Tivoli is not able to backup the open files - and database files are always open if SQL Server is running.
One option you have would be to restore the system databases to another SQL Server (not as system databases). Then, detach the databases from that other system and copy the mdf/ldf files to your restored system in the appropriate directories.
However, you are still potentially going to have problems - since you also have to make sure the other files are there and valid (e.g. the system resource database, certificates, etc...)
As you are finding out - shutting down your systems everytime you need to back up the system database files is not the easiest thing to get done. If you have that setup as a step in your upgrade plan, then everytime you apply the upgrade you can shut down SQL Server and copy the files. But, if you miss an update you are going to have a problem restoring and the restore time will be extended. This will happen because MS likes to apply patches to SQL Server (sometimes) through Windows Update - and they do slip through.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2009 at 4:50 pm
[p]SS Champion, you can't start SQL Server with the master files, so most of that posting did not apply to this situation. As suggested in one message, I have tried using system files from another server, but if any of the folder locations are different, it is a VERY tricky thing to do. Essentially, you need to create the same folder structure as the system you got the files from, put the files in place, start SS in special mode and perform a complex series of commands to change the settings to what the recovered server uses, then stop and restart, then clean-up the folders you no longer need. Screw up one step and you have to start all over again.
[/p][p]
That said, SyncBack sounds promising as a way to get the system DB files copies while SS is running. The only potential problem is that there is no guarantee the files will be properly synhronized. However, may be a feasible method if it is scheduled for a quiet time on the server.
[/p][p]
Thanks for the reference!
[/p]
March 13, 2009 at 4:56 pm
Sorry I missunderstood a bit; if you can't even start the SQL Server you have to first rebuild you master database.
You do that with following command using the setup files for SQL Server:
start /wait setup.exe /qn INSTANCENAME=
After it rebuilds the SQL Server should start up and you can use the RESTORE HEADERONLY to find the version of your master.bak files and patch your server back to the proper level.
And then restore master.bak, msdb.bak and model.bak.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 13, 2009 at 4:59 pm
Ten Centuries, the REBUILD option specifies that you need to reapply all updates. When I tried to reapply SS2005 SP2, it refused to install because there was a newer fix installed that had to be removed first. (GDR3054 had been installed by the server team via WSUS as a security fix I was not allowed to defer). The GDR removal failed partway through. I tried re-installed and re-removing, but the result was the same. At that point, I was faced with getting MS support on the line, and even with premium support, this problem introduced a delay that would NOT be unacceptable for some recoveries.
March 13, 2009 at 4:59 pm
larry Hennig (3/13/2009)
[p]SS Champion, you can't start SQL Server with the master files, so most of that posting did not apply to this situation. As suggested in one message, I have tried using system files from another server, but if any of the folder locations are different, it is a VERY tricky thing to do. Essentially, you need to create the same folder structure as the system you got the files from, put the files in place, start SS in special mode and perform a complex series of commands to change the settings to what the recovered server uses, then stop and restart, then clean-up the folders you no longer need. Screw up one step and you have to start all over again.[/p][p]
That said, SyncBack sounds promising as a way to get the system DB files copies while SS is running. The only potential problem is that there is no guarantee the files will be properly synhronized. However, may be a feasible method if it is scheduled for a quiet time on the server.
[/p][p]
Thanks for the reference!
[/p]
Which is why we decided to take the couple hour hit to re-install SQL Server.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2009 at 5:00 pm
Oops! Big typo. I meant to write "You can't start SQL server withOUT the master files being in place."
March 13, 2009 at 5:06 pm
Ten Centuries, I had left out the fact that we also make on-disk backups of all system databases and user databases, and those BAK and TRN files are recovered by Tivoli. The focus of this posting is how to get SQL server operational so those backups can be restored. You cannot restore master.BAK unless Master.MDF and LDF already exist because you can't start SQL Server without them. In SS 2000, there was a utility that built a generic master database for this purpose, but it was moved into the SETUP program as a REBUILD option and the documentation indicates that "some registry settings may be altered". Without more specific information about what is altered and what is not altered, and with the need to re-apply all fixes, I consider the REBUILD option to be too risky.
March 13, 2009 at 5:10 pm
larry Hennig (3/13/2009)
Ten Centuries, I had left out the fact that we also make on-disk backups of all system databases and user databases, and those BAK and TRN files are recovered by Tivoli. The focus of this posting is how to get SQL server operational so those backups can be restored. You cannot restore master.BAK unless Master.MDF and LDF already exist because you can't start SQL Server without them. In SS 2000, there was a utility that built a generic master database for this purpose, but it was moved into the SETUP program as a REBUILD option and the documentation indicates that "some registry settings may be altered". Without more specific information about what is altered and what is not altered, and with the need to re-apply all fixes, I consider the REBUILD option to be too risky.
Since you don't have master.mdf and master.ldf .. and you are not willing to REBUILD using the SETUP option; how do you plan to get database working?
If your server is down already; uninstall SQL Server doesn't hurt.
- Uninstall current install (it wouldn't care master.mdf/ldf is missing).
- Install a fresh install
- Use Restore Headeronly command to find out your version info from master.bak.
- Batch your fresh install to proper version
- Start sql server in single user mode.
- Restore master.
- Restore msdb.
- Restore model.
And you should be good to go.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 13, 2009 at 5:43 pm
LOL! I just realized "Ten Centuries" is a descriptor, not a handle. DOH!:D
It now seems clear that I need to be prepared for a REBUILD or re-install in the event that I do not have copies of system DB files for the required build, and that means keeping track of build levels and install options for each instance (and to think our relatively undisciplined server group thought they could manage SQL Server without any DBAs involvement!).
One complication in doing this is that we have some app+database servers that vendors have direct access to, and they have applied patches without our consent (I do not have the hammer needed to stop this, so I have to live with it).
As far as keeping the copies current ...
I am going to look into using SyncBak or something like it to update the copies of the system DB files whenever the build level changes.
I plan to code a job (or buy a tool) to alert us to changes in build numbers so we can keep a close eye on the situation and will have forensic evidence if something breaks as a result. This can also help me keep the reinstallation documentation up to date.
Our WSUS updates are applied once a month. Each server will be forced to reboot at that time regardless of whether WSUS demands a reboot, so I am thinking of using a boot-time script to update the copies.
I am confident my fellow DBAs will remember to refresh the copies when we manually apply updates, so it should be feasible to ensure the copies are kept current, and that it will be a rare situation when I need to re-install SQL Server to restore a server.
I just needed to know whether or not that effort would be a waste of time, and aside from opinions on whether or not a re-install is less work than the above, I think it is clear that the effort is worthwhile.
Thanks for the input!
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply