Troubleshooting a Failed SQL Server 2000 Database

Before You Restore A Database, You Need to Understand the Situation

Before engaging in any restore activity, you're going to want to follow these general steps to try to figure out what is wrong with the database.

You realize that the database has failed. What options are available to you?

1) Always start with the error logs. We looked at the Application log via the event viewer (in Windows 2000 "Start"-"Programs"-"Administrative Tools"-"Event Viewer". During one of our examples when we deleted the Northwind primary data file, we had the following errors on startup.

2) Double clicking on the errors reveals more specific information (that we are missing a data file):

Double clicking on error message indicates that a data file is missing

3) Clearly, as we suspected, we are missing one of the data files. We can go into Enterprise Manager and see that our database is suspect due to this problem.

Screenshot of Enterprise Manager showing that the Northwind database is grey (instead of yellow) and is labelled as being suspect.

4) We can also go into Query Analyzer and use the DATABASEPROPERTYEX function to determine what state the database is in. Here, we confirm that the database is in fact suspect. Suspect means that there is possible corruption, in our case we know we are missing a data file.

Screenshot of query in query analyzer - SELECT DATABASEPROPERTYEX('Northwind','status') - result is that the database is suspect

5) At this point, if we still didn't know what the problem was, we might attempt to use the DBCC CHECKDB command to try to repair the database. Books Online provides a lot of detail on the versatility of using DBCC in such a situation. In our troubleshooting situation, we do know that we're missing a data file and we need to restore from backup. If you're using the full or bulk-logged recovery model, now would be a good time to get a transaction log backup before kicking off the restore process.

Looking for SQL 2005?

I am currently working on SQL Server 2005 Backup and Recovery Exercises and will have the first exercises posted by 10/31/2008. SQL Server 2005 Backup and Recovery exercises will include GUI examples, T-SQL examples, and I'll also be including Video tutorials so you can easily follow along with the backup and recovery exercises. SQL Server 2008 exercises will be posted towards the end of 2008.

Contact Me

Please contact me with any suggestions that you have for the web site. I'd also appreciate any feedback on how the exercises worked for you, and how the site helped you put together a comprehensive backup and recovery plan - or even helped to save your data after a disaster. Contact Me Here.