Attaching/Detaching database with Multiple datafiles

Question

Post reply

Attaching/Detaching database with Multiple datafiles

nikhil.verma

SSCrazy

Points: 2603
More actions
May 26, 2010 at 12:52 am

#143347

Hi ,
On one of my production environment i have one database with 21 DATAFILES . the requirement is to detach and attach the database with the different drive. Please let me how to proceed with this activity ( the reason being i am asking as the db has multiple datafiles ) . Can you please advice on the steps.
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

VastSQL SSC-Dedicated Points: 36122 More actions · Answer 1

You can detach and move the files to new loation and attach ..You can specify the location when u use the GUI

nikhil.verma SSCrazy Points: 2603 More actions · Answer 2

Database Attach operation with sp_attach_db works well with the database having <= 16 datafiles. For more than 16 datafiles , we need to use CREATE DATABASE .. FOR ATTACH TSQL SCRIPT . Please correct me if am wrong.

VastSQL SSC-Dedicated Points: 36122 More actions · Answer 3

Have u tried using SSMS??

16 is right for sp_attach_db

nikhil.verma SSCrazy Points: 2603 More actions · Answer 4

The sp_attach_db stored procedure should only be executed on databases that were previously detached from the database server by using an explicit sp_detach_db operation or on copied databases. If you have to specify more than 16 files, use CREATE DATABASE database_name FOR ATTACH ----

Please refer -- http://technet.microsoft.com/en-us/library/ms179877.aspx

I need to understand what all things needs to be kept in mind before starting this operation.

Thanks