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 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)

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.

11 Responses to 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!

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