Scene 1
A call comes in from a client wanting to know if I had some time to look at his unresponsive database server. It could not be that bad I told myself. I few weeks ago I had explain to this same client that he needed to perform some serious maintenance on his database server; I assume that is why he was calling.
When I arrived the first thing I noticed was that nothing had changed. The IT person mentioned that he had tried to login to the system with no results. I was scared to ask how long this had been going on. With luck we could still get some good backups and salvage this setting. To our surprise we were looking at BSD (Blue Screen of Death), which I had not seen since my NT days. We had only 1 hour to fix what ever it was that we had to fix.
Scene 2
Server Configuration
• Dell P4 Server class, 2GB RAM and 2 80GB Hard drives.
• Hard Drive 1 Windows 2000 Server
• Hard Drive 2 SQL2000 databases and log files.
• No redundancy
Without knowing what had happen we bounced the server and hoped it would let us do minor recoveries. While we waited for the server to come up I asked IT for the most recent backups that they could restore as quickly as possible. Server started but within a few seconds it crashed again. No time to loose, IT had to rebuild the server. The second surprise of the day came along while the system was being restored. Only the user database was being backed up, which meant that all security settings, jobs and DTS configurations were lost. This was not going to be easy, granted this was not a big operation and some of the information I was going to need could be recover from the application vendor but we now had only 40 min. to restore this server.
Scene 3
What to do? Well, our first break came along in the form of a healthy data drive. The restore of the server had been completed and we could see the second drive data, all the SQL mdf and ldf files were there. I had 2 options, perform a radical recovery or a conservative recovery that will involve getting the vendor on the phone to recover at least 3 users the client application need to operate.
Since we had not change any hardware other than the hard drive I copy the Data directory to another location in the hard drive and re-installed SQL. I was going to try a Radical restore:
1. Make sure you have a copy of all the MDF files and LDF files.
2. Reinstall SQL2000
3. Stop all SQL2000 services
4. Copy all new files the DATA directory to another location.
5. Copy the old files on top of these newly created files.
6. Restart the services.
7. Update stats of all databases
8. Reassign the SQL server name
9. Backup all databases including master, msdb, tempdb
This is not the best way to recover a server but because of the lack of time and good backups this was the quickest way I though we could recover. If it caused problems we could use the new system databases and remap all ghost users.
Fortunately this worked, the client was happy and within 2 days he had a new server with redundant drives he wanted me to migrate the SQL server too.
This is what I recommended:
• Backup SQL system databases regularly
• Backup User database and log files regularly
• Add the sp_help_revlogin store procedure to all their SQL servers and run it periodically. This particular store procedure creates logins with the original SID and password which can be ran using Query Analyzer in SQL7 and SQL2000. Allow only Admins permissions to run this procedure.
READ: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q246133&ID=KB;EN-US;Q246133
• Script all Jobs and maintain copies of DTS packages on a development server if possible.
• Have practice runs recovering data.
Following these recommendations could save you from a total disaster.
