?
Solved

Move MDF, LDF, FT to another drive

Posted on 2010-11-16
19
Medium Priority
?
746 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 2000 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

765 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