Recovering From A Suspect Database

1. Firstly look in the SQL Server Logs in Enterprise Manager(EM). To get there, start EM and drill down from the Database Server à Management à SQL Server Logs. There should be multiple logs starting with Current and then followed by 6 or more archived logs.

2. Review all recent Error Log(s). There WILL be an indication here as to why the database has been marked suspect. You need to fix whatever the problem is first (i.e. missing file, permissions problem, hardware error etc.)

3. Then, when the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server.

4. To change the database status we will us the following store procedure: sp_resetstatus.
The steps are as follows:

a. USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
b.
sp_resetstatus
GO
-- Example: sp_resetstatus æWashingtonÆ

5. After the procedure is run, immediately disable updates to the system tables:

a. sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

6. Stop and Restart ALL SQL Server Services

7. If the database still goes back into suspect mode, and you can't fix the original problem, and you have no recent backup, then you can get information out of the database by putting it into Emergency Mode. If you do this, extract the data/objects out with DTS and rebuild the database. Note that the data may be corrupt or transactionally inconsistent. You WILL NOT be able to use this instance of the database after it is put in to Emergency Mode besides pulling data out of it !!!

8. Issue the following command to put the database into emergency mode:

a. USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

b. UPDATE master..sysdatabases
SET status = 32768
WHERE name = 'DatabaseName'

9. Stop and Restart ALL SQL Server Services.

10. We are now ready to pull whatever data we can out of the tables in the corrupt database. Remember, some tables may be corrupt, thus you may have to play with various T-SQL statements to get the data out. First try DTS à

11. These are the steps necessary to export data out of the corrupt database into the new:

a. Create a new production DB, or a temp DB, to have a place to export whatever data we can out of the corrupt db.

b. Start a DTS operation by going into EM and drilling down to ôData Transformation Servicesö à ôLocal Packagesö.

c. Open a NEW DTS package by right-mouse clicking à

d. When DTS opens, choose ôCopy SQL Server Objects Taskö from the Connection Icons. Enter in a description like ôExport Corrupt Dataö. Enter in the SA/pass combination as well as the CORRUPT database from the drop-down.

e. Select the ôDestinationö Tab. ö. Enter in the SA/pass combination as well as the PRODUCTION database from the drop-down.

f. Select the ôCopyö Tab. UNCHECK the ôCreate destination objectsö box. UNCHECK the ôCopy all objectsö box and then Click on the ôSelect Objectsö Button. This brings up the ôSelect Objectsö screen.

g. CHECK ONLY ôShow all tablesö like shown above. Then check each table that needs to be exported. If ALL tables need to be export, Click on the ôSelect Allö button. Click OK.

** If ALL objects are to be recovered, Select ALL Objects by check marking them and then click on the ôSelect Allö button. This will grab everything possible.

h. Click OK again and we are done creating this task. Now we execute the package by Clicking the green arrow on the menu bar.

12. Restore Data Issue: Restoring the data into the Production database is dependent on what time of day it is. If it is during ôHotö times, high playing times, restore the data during a slow period or close of gaming day!

If Microsoft Tech support is to be called, it is advisable to get the log files ready to be emailed to the tech for review. The process to accomplish this is as follows:

1. Go to the Bin folder located under the SQL Server installation folder. In this folder you will find an application called SQLDIAG.exe.

2. Execute SQLDIAG.exe. This app is ôsupposeö to zip all the log files into a file called SQLDIAG.txt in the Log folder under the SQL Server installation folder. This operation did NOT work for us.

3. We went directly into the Log folder of SQL Server and used PKZip to zip the files. We then emailed them directly to the tech.