How to move log shipped secondary database files

By SQLRx Admin | SQL Server

Jan 30

One of our clients wanted to consolidate files on one of their servers.  In doing this I realized that I was going to have to move the files for a database that is actually log shipped.  This is a fairly easy task that is pretty well documented but since I had to do it I thought “What the hey!  I should blog about this.” so here we are.

The main problem is how to do this without breaking log shipping in some way.  If you have a huge database that can take forever to restore and reset log shipping you will want to do this the right way so you don’t have to slink to your IT Manager to say that reporting or whatever will not be available while you fix things.  Not that I would have any experience with that….

Whatever you do, don’t  think you can move the log shipped database by simply detaching then attaching again.  This will not work and you will be making the walk or call of shame to your IT Manager.  You can move the files after doing a few more steps that I have outlined below.  Now go have at it!

1. Get the location of the secondary database files in case you don’t already know where they are.  You will need the logical name later on.

SELECT name AS DBLogicalName,
physical_name AS Location,
CASE type WHEN 0 THEN ‘Database File’ WHEN 1 THEN ‘Transaction Log’ END AS FileType,
state_desc AS [State] FROM sys.master_files
WHERE database_id = DB_ID(‘LS_DB_NAME’)

2. Disable the LS_RESTORE job for the database so that no logs are restored until after the files are moved.

USE [msdb] GO
EXEC msdb.dbo.sp_update_job @job_name=N’LSRestore_??_??’, @enabled=0
GO

3. Make sure the database is in NORECOVERY state if it is currently in STANDBY state.  If the database is already in NORECOVERY state then you don’t need to run the below code.

USE [master] GO
RESTORE LOG LS_DB_NAME WITH NORECOVERY
GO

4. Execute the ALTER DATABASE command to tell SQL where the files will be located soon.

USE [master] GO
ALTER DATABASE LS_DB_NAME
MODIFY FILE ( NAME = DBLogicalName, FILENAME = ‘Path to new location’)
GO

5. Stop SQL Server services including the SQL Agent service.

6. Use Windows Explorer to move the files to the new location.

7. Restart the SQL Server services including the SQL Agent service.

8. Enable the LS_RESTORE job for the database.

USE [msdb] GO
EXEC msdb.dbo.sp_update_job @job_name=N’LSRestore_??_??’, @enabled=1
GO

9. Verify that all log shipping jobs are still working.

For those of you who have log shipped secondary databases in STANDBY and are wondering if it will go from NORECOVERY to STANDBY again….the answer is that it will as soon as the LS_RESTORE job runs and restores transaction logs again.

About the Author

>