Geeks With Blogs
David Blomqvist

I was working with a system migration where a client had moved a database server. The actual SQL Server HW was moved but not the SAN where the database MDF’s and LDF’s resided.

They solved this by copying all of the data and log files onto a portable (USB) disk and sending this with the server. These files where then to copied onto the SAN at the new hosting place.

But, they did not detach the databases first. Just copied the files. So when the server came up on the new location all the databases were useless since they had no files. At first when I saw this I was quite worried.

Since SQL Server does not open up a database until someone requests it did know (and show) that all of the databases where corrupt/illegal since they where lacking there data and logs.

The error message that you get is:

“Database ‘Yourdatabase’ cannot be opened due to inaccessible files or insufficent memory or disk space”.

SQL-Error dialog “Database ‘Yourdatabase’ cannot be opened due to inaccessible files or insufficent memory or disk space”.

 

I first tried to detach the database from SQL server to be able to attach them again. This was not possible. But then I found a solution.

First step is to take the database offline. Then detach the database. It seems like this bypasses all interaction with the database, so it is possible to do this even with an “corrupt” database.

After that it was possible to attach the database again by pointing out the files on the new SAN.

summary

Case: the database is i an illegal state lacking it’s files.

1. Take the database offline

2. Detach the database

3. Attach the database with the new files. (*)

 

(*) When you select a data file then SQL will try to find the log file in the old location and mark it as “Not found”. Browse to the new location of the log file. note that there seems to be a little bug in SQL 2005 here, the log file will still be marked as “not found”. Do not worry, just click OK and it will be attached.

Posted on Thursday, June 11, 2009 12:30 AM | Back to top


Comments on this post: SQL Detach / Attach

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © blomqvist | Powered by: GeeksWithBlogs.net