SQL Server Backup Info: Troubleshooting
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):
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.
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.
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.
