Attaching database that wasn’t detached


I recently had an interesting issue where a Virtual SQL Server was deleted from VMWare, just like that with no nice shutdown and certainly no last backup & detach database commands. We were fortunate to locate and mount the data and log disks but the SQL/OS partitions were lost, so there was no way to get it back online again.

I was asked to get the databases back up again, so I got a new virtual SQL Server with the data and log disks mounted.

My first attempt was to try attaching them, just like I would with a detached db, but Microsoft clearly states in the BOL that an operation like that is not possible. In order to attach a datafile, it must first have been detached.

Knowing that the datafiles should be ok I started testing ways to trick the SQL Server into accepting my files.
The solution was to create an empty database, with the same name, number of data and log files and also roughly the same size (I chose a little bit larger) as the ones I wanted to attach.
When created, I took the db Offline. Switched the data and log files with the ones i wanted to use and then took it Online again.

This seems to have worked for me, no errors in errorlog, dbcc concistency checks worked out fine and when presenting the databases to the end users they were glad to find everything just as how it was left.

This was on a 2008 R2 Developer edition on Windows Server 2008 R2.

This entry was posted in DBA, SQL Server, VMWare. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *