(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.)
As a follow-up to my previous post Disaster recovery 101: backing up the tail of the log, I want to describe how to re-attach a damaged database if someone’s detached it accidentally while performing disaster recovery. A couple of years ago I blogged a detailed post on Creating, detaching, re-attaching, and fixing a suspect database but now I’d like to do a short, concise post for reference.
There are many reasons why you may have a damaged database that you’re trying to attach to SQL Server:
- It may be a SUSPECT database that someone is erroneously trying to fix by detaching+attaching
- It may be a damaged database from a server that is inaccessible that needs to be attached for disaster recovery
- It may be a database you’ve been sent/given that was not cleanly shut down (i.e. there were active transactions) and you were not given the transaction log file
In any of these scenarios, a damaged database cannot be attached to SQL Server using the normal methods: sp_attach_db, CREATE DATABASE … FOR ATTACH, or CREATE DATABASE … FOR ATTACH_REBUILD_LOG (the latter two being in SQL Server 2005 onwards). Either the data file damage will prevent the attach, or the log file damage will prevent crash recovery from being started or completed.
To attach the database you need to fool SQL Server into thinking it’s already attached. The set of steps to go through are as follows:
- Create a dummy database, with the same name, the same number of data and log files, with the same file names and same file IDs (make sure you have instant file initialization enabled so the file creations don’t take ages – see this blog post). This can be tricky to do if you’ve added removed files, but you need to make sure the file IDs are exactly the same.
- Set the dummy database offline (ALTER DATABASE SET RealDatabaseName OFFLINE) or shut the server down
- Delete all the data and log files from the dummy database
- Drop in as many of the data and log files from the damaged database as possible
- Set the dummy database online (ALTER DATABASE SET RealDatabaseName ONLINE) or start the server if you shut it down for step 2
At this point SQL Server will be fooled into thinking the database is attached (as it thinks it’s just restarting the dummy one you created) and you’ll be able to take further measures to recover the database.
Further measures may include:
- Taking a tail-of-the-log backup to use as part of a restore sequence (see this blog post)
- Use EMERGENCY mode to extract data into a new database
- Using EMERGENCY mode repair to repair a damaged transaction log (see this blog post)
Note: it’s entirely possible that your database may be so damaged that not even EMERGENCY mode repair can fix it. If you cannot even get the database into EMERGENCY mode (e.g. if the database boot page, or the primary file’s file header page is damaged) then there is no way for SQL Server to access the database. At this point the only option is to restore from backups. If you don’t have backups, game over.
Hope this helps!