[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Move MDF, LDF, FT to another drive

Posted on 2010-11-16
19
Medium Priority
?
747 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 70

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 70

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

650 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