Solved

Move MDF, LDF, FT to another drive

Posted on 2010-11-16
19
738 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
  • 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 21

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
 
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:ScottPletcher
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:ScottPletcher
ID: 34160389
If you can solve the *%^&( permissions issues after the detach, you should be able to script the rest.
0
 
LVL 21

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 21

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 21

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 21

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 21

Author Closing Comment

by:yo_bee
ID: 34265168
Responsive.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now