?
Solved

Moving an LDF to a seperate physical location

Posted on 2007-10-16
7
Medium Priority
?
322 Views
Last Modified: 2012-06-21
All,

I am a novice in SQL so please remember that

I wish to use the gui to detach and reattach a DB, this in itself is no problem and works fine. However I want to be able to move the MDB file to one location and the LDF to another. The detach/reattach process only allows me to put the MDF/LDF to the same location.

is there a way (in gui) to do this....

Thanks
0
Comment
Question by:credmood
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 600 total points
ID: 20091222
with the attach/detach interface, you cannot do it.
you need either to use the stored procedure sp_attach_db, where you can specify the locations explicitely, or use the backup + restore method, where during the restore you can specify new locations per file individually.

0
 

Author Comment

by:credmood
ID: 20091354
Thanks
In your opinion which is the best way?

I'm thinking the stored procedure way..if so what are the command variables to do this

Cheers
0
 
LVL 8

Assisted Solution

by:matrix_aash
matrix_aash earned 200 total points
ID: 20091523
The best approach is to use the backup and restore method as its pretty easy way to change loaction of MDF/LDF files.
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!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20091729
I prefer the backup + restore method, as that has the implicit security of having a backup!

anyhow: the syntax of sp_attach_db:
http://msdn2.microsoft.com/en-us/library/aa259611(SQL.80).aspx
0
 
LVL 5

Assisted Solution

by:Crag
Crag earned 600 total points
ID: 20092212
On SQL 2005 the easiest way to move the files is the CREATE DATABASE.. FOR ATTACH command:

USE [master]
GO

CREATE DATABASE [MyDB] ON
        ( FILENAME = N'E:\MyDB\data.mdf' )
      , ( FILENAME = N'F:\MyDB\TRLog.ldf' )
 FOR ATTACH_REBUILD_LOG
GO

On sQL 2000 use:

EXEC sp_attach_db @dbname = N'MyDB'
      , @filename1 = N'E:\MyDB\Data.mdf'
      , @filename2 = N'F:\MyDB\TRLog.ldf'


You can change the database name, and the names of the files to suit your environment.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 600 total points
ID: 20093732
>> In your opinion which is the best way? <<

The size of the db is a factor here.  Backup & restore requires, well,  a backup and a restore :-) .  For a large db, this can be a significant amount of time.

The detach / attach using pre-written commands can be done in seconds for any size db.  However:

** Before detaching the db, run a CHECKDB on it!  A damaged db cannot be re-attached!! **

Note that the db can be read/written to during the check.
0
 

Author Comment

by:credmood
ID: 20099074
Thanks for this....

A few of the DB's are large a few are small..I think its horses for courses...small ones backup/restore the larger ones detach/reattach ...
Ill just have a play with a few test DB's
Thanks for all your help..
I hope your happy with my points sharing


0

Featured Post

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!

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 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