Steps to Move SQL Server Log Shipping Secondary Database Files

By:   |   Comments (8)   |   Related: > Log Shipping


With SQL Server is it possible to move the secondary database involved with Log Shipping to a different drive without disturbing the Log Shipping configuration?  If so, what are the steps to accomplish this task?  Check out this tip to learn more.


There are scenarios where you have to move the Log Shipping secondary database to different drive due to disk space issue or to do the maintenance of the drive. In this tip I will show you how we can move the Log Shipping secondary database without disturbing/reconfiguring SQL Server Log Shipping.

To configure the Log Shipping please check Step by Step SQL Server Log-Shipping Setup

Before we start moving secondary database files, let's take a look at the secondary database restore mode. In Log Shipping the secondary database can be configured for Standby or NoRecovery mode.  Here is a brief explanation:

  • Standby Mode: In Standby mode a user can execute SELECT commands and the transaction logs restores need to be coordinated with the SELECT commands.
  • NoRecovery Mode: In NoRecovery mode secondary database status is always in restoring state and users cannot issue SELECT commands.

You can execute the below query on the Log Shipping Secondary SQL Server to determine the restore mode information:

--Execute the below script on the secondary server
declare @databaseName varchar(300)
set @databaseName = 'myLogShip' -- Secondary Database Name
-- 0 = Restore log with NORECOVERY.
-- 1 = Restore log with STANDBY.
select secondary_database,
case restore_mode 
when 0 then 'No Recovery'
when 1 then 'Stand by' end AS 'restore_mode'
from msdb.dbo.log_shipping_secondary_databases 
where secondary_database = @databaseName

To check the current location of the secondary database files location, execute the below query on the Secondary SQL Server.

--Execute the below script on the secondary server
declare @databaseName varchar(300)
set @databaseName = 'myLogShip' -- Secondary Database Name
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(@databaseName);

To move the database to different drive, we will use the ALTER DDL command.

ALTER DATABASE database_name 
MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

To move the secondary database files, your secondary database restore mode must be in No Recovery state. If the database state is Standby Mode, your ALTER statement to move the database files will fail with the below error.

Msg 5004, Level 16, State 4, Line 1
To use ALTER DATABASE, the database must be in a writable state 
in which a checkpoint can be executed.

To avoid the above situation you need to change the database restoring mode to NoRecovery as shown below as Step 1.  If the secondary database is already running No Recovery mode than you can ignore this step.

Step 1: In SQL Server Management Studio, right click on the primary database -> select Properties -> click on the Transaction Log Shipping page -> click on the Secondary server instances and database configuration ellipse (...) as shown below and it will open the Secondary Database Setting dialog box which is the second image below.

Transaction Log Shipping page

Click on the Restore Transaction Log tab and select No recovery mode radio button in Secondary Database Setting dialog box as shown below. Database recovery mode will change to No recovery mode on next transaction log restore.

Secondary DataBase Settings

Step 2: Once the secondary database state is changed to No Recovery Mode, disable the Log Shipping Restore Job on secondary server. To disable the job in SQL Server Management Studio, navigate to root | SQL Server Agent | Jobs | Log Shipping Restore Job then right click on the job and click the Disable option as shown below.

Disable LS Restore Job

Step 3: On the Secondary Log Shipping SQL Server, execute the ALTER database command as shown below to identify the new location of the secondary database and log file.

-- Execute the below script on secondary server 
-- Specify the secondary database, file name and new location
MODIFY FILE ( NAME = myLogShip, FILENAME = 'D:\SQLMonitor\myLogShip.mdf' );
MODIFY FILE ( NAME = myLogShip_Log, FILENAME = 'D:\SQLMonitor\myLogShip_Log.ldf' );

Step 4: Stop the Secondary SQL Server instance services. Go to SQL Server Configuration Manager and stop the secondary instance services.

Step 5: Move the Log Shipping Secondary database files to the new location in Windows Explorer as mentioned in step 3.

Step 6: Restart the secondary instance SQL Services in SQL Server Configuration Manager.

Step 7: Enable the Log Shipping database restore SQL Server Agent Job on the Secondary SQL Server see step 2 as a point of reference.

Step 8: Verify the log shipping SQL Server Agent jobs are working properly.

Next Steps
  • Document your SQL Server Log Shipping configuration for all servers.
  • Monitor your storage utilization and validate there is enough storage on the target location to move the SQL Server database.
  • Check out these related tips:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, February 1, 2023 - 5:03:19 AM - Joakim Agermark Back To Top (90870)
This worked for me. But strange when I take properties on the database on secondary and look at Files tab. The path to the files are still showing the old path. But when running tsql command to show files place it shows the new correct location. Why?

Tuesday, April 24, 2018 - 7:43:47 AM - Ajay Prakash Back To Top (75767)

Two points:

1. We are impacting the entire server by restarting SQL Server, however this is not too bad and can be worth for big databases otherwise for small ones probably conventional strategy(backup restore) will be worth of avoiding unnecessary complexity.

2. Wonder why did you forget to setup the databses back in STANDBY mode?

Saturday, October 17, 2015 - 8:59:18 AM - Christian Back To Top (38920)

I have a database with 2 file groups that span across 2 separate drives. When I am restoring a database through T-SQL or the GUI, I use the WITH MOVE option. I don't have enough initial capacity on a single drive to restore both groups so need to move them at the point they are restoring. Is this possible?

Sunday, August 30, 2015 - 7:01:53 PM - David Back To Top (38567)

You can do it in standby.  Just set it offline first, then issue the alter database ... modify file command.  Then move the file and bring the database online again.

Wednesday, May 27, 2015 - 1:52:49 PM - Mehrdad Back To Top (37293)


Hi Jugal,

Thanks for providing such a solution. However please let me know can I use Detach\Attach instead of steps 3 and 4? (by considering to avoide getting the error message "Cant attach database, it was being under restore").

Monday, October 27, 2014 - 3:36:31 AM - Arindam Das Back To Top (35067)


I need your help. All  my logshipping and replication are configured with a specific login id ie. a windows authentication eg. pispl\arindam.das. Now I want to change the password of the same. Will it hampared my logshipping and replication ? Or if I need configure logshipping and replication with different user id what are difficulties I will face. Please these server are production server.


Thnx n rgds


Wednesday, February 20, 2013 - 12:40:28 PM - PAPA SARR Back To Top (22310)

Thank you for your post.  It is very useful.

However, I have another question related to log shipping.  I am trying to add another secondary database.  Would you know the steps to follow?  I believe I will need a full backup of the primary database, and I don't know if it will break the LSN of the transaction log.

Wednesday, January 9, 2013 - 6:40:30 AM - Karthick Back To Top (21338)

This is very useful topic. thanks a lot.

get free sql tips
agree to terms