Moving data files

[gharabed]

Question...I have detached a database that has over 70 files. I've relocated them to various new locations. How do I re-attach the database if re-attaching the database only accepts 16 datafiles? Is there a different method I can use for relocating the data files?

 

[mrdenny]

You need to use the CREATE DATABASE command using the FOR ATTACH paramater to attach a database with more than 16 files.


More information can be found here

http://support.microsoft.com/kb/885712/en-us

You can also look in Books Online under CREATE DATABASE about 1/6th of the way down the page at the FOR ATTACH section.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

And don't be surprised if the Create Database..For Attach takes FOREVER. I've only got 18 files and it takes over an hour to get them all reattached.

But if you're not patient and try to kill out of it, you could potentially corrupt your files. So, just wait it out. It'll take a while.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gharabed]

I ended up just using sp_attach_db 5 times along with sp_detach_db and just accepted the 16 file limitation.