April 22, 2011 at 7:52 am
I need to move the log file for a database to a different drive, what is best way to do this? i was going to use detach\attach method
April 22, 2011 at 8:26 am
dajonx (4/22/2011)
Please verify this, but I believe you can do a modify file to the new location, then do a restart on SQL service via the Configuration Manager.
If you just want to move one database there is no reason to take down the whole SQL instance to do it.
The detach\attach method is the best method if you just need to move it for one database.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 22, 2011 at 9:31 am
Awesome, thanks.
April 22, 2011 at 10:07 am
Shawn Melton (4/22/2011)
The detach\attach method is the best method if you just need to move it for one database.
Disagree...
You can move a database file using ALTER DATABASE, then take just that database offline, move the file, then bring the DB back online. Better solution if there's CDC, replication or other such things configured.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2011 at 10:08 am
I prefer using ALTER DATABASE ... MODIFY FILE to move database files. This method preserves the database ownership and does not require a detach/attach of the database.
The first step is to modify the file location:
ALTER DATABASE {your database} MODIFY FILE (filename = {new file location and name});
GO
Then, you take the database offline:
ALTER DATABASE {your database} SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
Once the database is offline, copy the file to the new location and name and then bring the database online:
ALTER DATABASE {your database} SET ONLINE;
GO
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 26, 2011 at 3:46 pm
If you're just moving a secondary log file...
1. create a new log file in the new location.
2. Do a shrinkfile with EMPTYFILE option on the log file that is to be moved.
3. Delete the old log file.
This does not work with the primary log file(the inital one that gets created). Even though the MSDN seems to imply that you can. See http://msdn.microsoft.com/en-us/library/ms191433.aspx. You will get a error 5020: The primary data or log file cannot be removed...
September 6, 2017 at 9:49 am
I know last post was 6 years ago 🙂
Just wanted to clarify that if using the ALTER DATABASE MODIFY FILE method, then taking the db offline as some of the posters have described above, you must COPY the file to a new location, not MOVE it, or you will get an error. This is my script:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'TestLogShip');
USE master; --do this all from the master
ALTER DATABASE TestLogShip
MODIFY FILE (name='TestLogShip'
,filename='E:\MSSQL\TestLogShip.mdf'); --Filename is new location
ALTER DATABASE TestLogShip
MODIFY FILE (name='TestLogShip_log'
,filename='E:\DBA_TEST\TestLogShip_log.ldf'); --Filename is new location
ALTER DATABASE TestLogShip SET OFFLINE WITH ROLLBACK IMMEDIATE
--- COPY the physical files !!!
ALTER DATABASE TestLogShip SET ONLINE;
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'TestLogShip');
September 7, 2017 at 6:46 am
GilaMonster - Friday, April 22, 2011 10:07 AM......, then take just that database offline......
awesome suggestion .
September 7, 2017 at 7:38 am
Under this link https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases (see Planned relocation Procedure), Microsoft say:
1. Take the DB offline
2. Move the files to the new location
3. Run ALTER DATABASE ... MODIFY FILE ... for each file moved
4. Bring the DB online
I am curious whether you should take the database offline first, or run ALTER DATABASE MODIFY FILE first.
September 7, 2017 at 7:51 am
William Rayer - Thursday, September 7, 2017 7:38 AMUnder this link https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases (see Planned relocation Procedure), Microsoft say:1. Take the DB offline
2. Move the files to the new location
3. Run ALTER DATABASE ... MODIFY FILE ... for each file moved
4. Bring the DB onlineI am curious whether you should take the database offline first, or run ALTER DATABASE MODIFY FILE first.
i run the alter statements first usually
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply