Solved

Move MDF, LDF, FT to another drive

Posted on 2010-11-16
19
742 Views
Last Modified: 2013-11-05
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
Comment
Question by:yo_bee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +2
19 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 34151710
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
 
LVL 23

Author Comment

by:yo_bee
ID: 34152269
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34152522
>> 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34152571
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34160382
>> 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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34160389
If you can solve the *%^&( permissions issues after the detach, you should be able to script the rest.
0
 
LVL 23

Author Comment

by:yo_bee
ID: 34160538
So I can use xp_cmdshell within ssms?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34160609
>>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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34160982
>> 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
 
LVL 23

Author Comment

by:yo_bee
ID: 34161277
I was hoping to batch this. Maybe I can script with vb. Try to pass SQL statements via that.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34161492
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
 
LVL 23

Author Comment

by:yo_bee
ID: 34161534
One time, but the same server and the same instance.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34161791
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34210007
Unfortunately the author has not provided adequate feed back to the questions asked.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34215114
>> 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
 
LVL 23

Accepted Solution

by:
yo_bee earned 0 total points
ID: 34215293
I manually detached each DB,  moved the mdb and ldf to the new location and attached the db when completed.

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34218808
>> 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
 
LVL 23

Author Closing Comment

by:yo_bee
ID: 34265168
Responsive.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question