Easy Steps to 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.
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
- In secondary database setting window, click on the Restore Transaction Log tab and select No recovery model.
- 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
- 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
- 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.