• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

Move MDF, LDF, FT to another drive

Is it possible to script a bulk mdf,ldf, & full text relocation. From what I researched it seems that you would need to detach , then move the files, then attach all manually.
I was hoping to locate statement or procedure to loop through the DB's and move them.

0
yo_bee
Asked:
yo_bee
  • 7
  • 6
  • 2
  • +2
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Since you are preferring TSQL script, this can help you out:

http://blog.sqlauthority.com/2007/08/24/sql-server-2005-t-sql-script-to-attach-and-detach-database/

Of course, you can also do it via GUI via SSMS by Right clicking Database and Choose Detach and then Attach it again.
0
 
yo_beeDirector of ITAuthor Commented:
the goal is to have the server down for maintenance and run a bulk statement.
I am trying to avoid one by one basis process.

I like the  Blog. but --step 2 has nothing oin it.
That is the step I am looking for.

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> but --step 2 has nothing oin it.

Step 2 is a manual one and can't be done via SQL Scripts.
If you want to do everything in a single step, then use Copy Database wizard to restore that database into some other name on your instance itself. It would do all things in a single shot and just rename the destination database which would suffice.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bhavesh ShahLead AnalysistCommented:
Hi,

RajanSir already give u answer but still check out following link.

http://www.mssqltips.com/tip.asp?tip=1688

http://www.mssqltips.com/tip.asp?tip=1774

It may helps
0
 
Scott PletcherSenior DBACommented:
>> Step 2 is a manual one and can't be done via SQL Scripts. <<

That's not actually true.

I have it fully scripted for SQL 2000, but on SQL 2005, the sp_detach changes the file permissions, [WHY?WHY?WHY?], which makes the script problematic for SQL 2005.

I used dynamic SQL, including:
sp_detach
xp_cmdshell COPY [or XCOPY] /Y ...
sp_attach

With variables for the:
1) destination data directory
2) destination log directory

And, in my case, gazillion switches to make the script have built-in checking (or not).  Such as, "is the db busy so can't be detached?", etc..
0
 
Scott PletcherSenior DBACommented:
If you can solve the *%^&( permissions issues after the detach, you should be able to script the rest.
0
 
yo_beeDirector of ITAuthor Commented:
So I can use xp_cmdshell within ssms?
0
 
Anthony PerkinsCommented:
>>So I can use xp_cmdshell within ssms? <<
It depends.  
Is it enabled?
Do you have permission to use it?
Do you have the appropriate permissions for the command you are going to execute?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> the sp_detach changes the file permissions, [WHY?WHY?WHY?], which makes the script problematic for SQL 2005.

Have tried this with 2005 and hence I told that it would be better to copy files manually and take care of the permissions issues.
0
 
yo_beeDirector of ITAuthor Commented:
I was hoping to batch this. Maybe I can script with vb. Try to pass SQL statements via that.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If it is a One-time activity, then you can do it easily with Copy Database wizard..
In the meanwhile, have you planned to do this iteratively since you are planning for a script.
0
 
yo_beeDirector of ITAuthor Commented:
One time, but the same server and the same instance.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Then use the copy database wizard and copy the database to the same instance.
Since database name is already used, set the database name as name_copy and once successfully copied, you can change it back to name from name_copy.
While using Copy Database wizard, just make sure that you provide the locations for MDF, LDF and Full text catalogs, else it would be copied to the same location itself.
0
 
Anthony PerkinsCommented:
Unfortunately the author has not provided adequate feed back to the questions asked.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Each of the answers was something I was trying to avoid using.  

Then can you post what you have achieved or done finally and accept that as a solution instead of deleting this question.
What we have suggested is the easier way of solving your problem..
0
 
yo_beeDirector of ITAuthor Commented:
I manually detached each DB,  moved the mdb and ldf to the new location and attached the db when completed.

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I manually detached each DB,  moved the mdb and ldf to the new location and attached the db

Is this what I suggested in my very first comment http:#a34151710
If you want to, then accept your last comment as a solution and I don't have any issues.
0
 
yo_beeDirector of ITAuthor Commented:
Responsive.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now