SQL Server Log Analyzer

Easy Steps to Move Log Shipping Secondary Database File

Stacy Brown ~ Modified: March 20th, 2023 ~ SQL Transaction Log ~ 3 Minutes Reading

move-log-shipping-secondary-database-file

In the earlier article we have discussed about SQL server transaction log shipping implementation. Now, in this blog post we will discuss how to move log shipping secondary database file to a different drive without affecting the log shipping configuration. If you think you can move easily by detaching the log shipped database and attaching again, then you are wrong this will not work.

Table of Content

Error Statement

Before I will tell you the procedure to move log shipping secondary database file, make sure that your database restoring mode is No Recovery mode. If your database is in Standby mode, then your ALTER statement shows the following error.

error msg 5004

Move Log Shipping Secondary Database File This Way

To change the recovery model open SSMS (SQL Server Management Studio) & then start SQL server move log file task as mentioned below:

  • Right click on the primary database and select properties
  • Under select a page column, click on the Transaction Log Shipping
  • Check the Enable this as a primary database in a log shipping configuration box and after that SSMS will open the Secondary server instance and database

Move-log-shipping-secondary-database-file

  • In secondary database setting window, click on the Restore Transaction Log tab and select No recovery model.

Secondary database setting

  • After changing the database status into no recovery model, disable the job in SQL Server Management Studio. To disable jobs click on the SQL Server Agent under the Object Explorer column>> select jobs >> then right click on the job and click Disable

Disable the job in SQL Server Management Studio

  • Use the ALTER database command as shown below to identify the new location of the secondary database. You have to specify the secondary database, file name and location

ALTER database command

  • From the SQL Server Configuration Manager stops secondary SQL Server services including SQL Agent service
  • Move Secondary database file to the new location by using Windows Explorer and restart the SQL Server Services including SQL Agent service
  • Enable job on the Secondary SQL Server
  • Verify that all log shipping jobs are working properly

Also Read: How to Resolve SQL Server Error 3159 Safely

Conclusion

Finally, after analyzing all the crucial aspects of the blog, we can say that this task is very easy for both beginners as well as expert SQL server users. Also, to move log shipping secondary database file, this guide offers the best solution to users manually. However, automated solutions are there as well that work even better.