(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:
- This only works if the server instance you’re using is the same version as the database you’re trying to attach
- Create a dummy database, with the same name, the same number of data and log files, with the exact same file names (extremely important!) 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 had added or removed files to the database before it was corrupted, but you need to make sure the file IDs are exactly the same.
- Set the dummy database offline (ALTER DATABASE RealDatabaseName SET 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 RealDatabaseName SET 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!
9 thoughts on “Disaster recovery 101: hack-attach a damaged database”
Thanks for this wonderful article ! I have expereinced that SQL Gurus like you stress on having good backups and most of the newbies find this thing as monotonous and repetetive.They feel that in a disaster situation they will run few commands,do some attach-detach and can bring the database up.However,in crisis most of the times it is the backups that help us.Thanks for highlighting this often ‘BACUPS ARE OUR BEST FRIEND’
Yeah, went through this yesterday I managed to get two out of twenty some-odd databases recovered on a water damaged server. Even then, we ended up building a new server and restoring everything there. There is no substitution for a recovery plan!
Another Excellent Article
Thanks a lot, I like this
Why do the files in the dummy db need to be the same size of the original db? I managed to get it working with tiny files in the dummy db.
I also used this method for another purpose – a customer required to periodically set up a read-write copy of db from a logged shipped secondary db (instead of copying a new full backup from the production server which is on another continent) and since SQL Server doesn’t allow backing up a database in standby (or norecovery) mode, that’s the only way.
Do you think there’s a technical reason not to allow us to take backups of norecovery/standby databases? I think that it can be an awesome feature for SQL Server to allow us to take full (and differential) backups off the secondary servers.
@Saggi It used to be the case that the files had to be around the same size – I should check to see whether that still holds. As far as the backup idea is concerned, yes – it would be a neat feature and could be achieved with a bit of coding in the server. Cheers
Apparently there’s already a suggestion in Connect regarding backup of standby feature here:
you are awesome – you saved my ass
I was able to attach an MDF-only database using different filenames. The one thing I had to do was make sure the original *DIRECTORY PATH* existed for the LDF that was missing.