Problem
We recently got multiple out of sync alerts for one of our log shipped databases which became out of sync because a new database file was added to the primary database. When we checked the error log, log shipping was not able to apply the remaining log backups because it was not able to find the drive/path where the new database file was created. In this tip, I will describe a step by step method to re-initialize a log shipped database that is impacted by such a change.
Solution
If the database file path is identical on both servers then this type of issue will not appear. You can avoid this type of issue by making sure that both the primary and secondary databases have the same file paths available.
In the following steps, I will walk through a scenario where the paths did not exist on both servers.
Steps to Add a New Database File to a Log Shipped Database
In our example, the primary server uses the E: drive and the secondary server uses the D: drive. This is what our issue resulted from.
Step 1
Add a new database file to the primary database. Run the statement below to add a new database file to your primary database.
ALTER DATABASE Manvendra ADD FILE (NAME = Manvendra_test,
FILENAME = ‘E:\Program Files\Microsoft SQL Server\MSSQL10_50.MANVENDRA\MSSQL\DATA\Manvendra_test.ndf’,
SIZE = 1000MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
Go
Step 2
Once you have added the new database file, then run the logshipping backup job to capture these transactions. Once the backup is completed run the logshipping copy job to copy this backup file to the destination folder to restore it on the standby database.
Step 3
Run the logshipping restore job on the secondary server to restore the recently created backup file to apply the changes we made on the primary server. Unfortunately, the restore job will fail to complete with the errors below:
2012-11-25 20:34:17.35 *** Error: The operating system returned the error ’32(The process cannot access the file because it is being used by another process.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘E:\Program Files\Microsoft SQL Server\MSSQL10_50.MANVENDRA\MSSQL\DATA\Manvendra_test.ndf’.
File ‘Manvendra_test’ cannot be restored to ‘E:\Program Files\Microsoft SQL Server\MSSQL10_50.MANVENDRA\MSSQL\DATA\Manvendra_test.ndf’. Use WITH MOVE to identify a valid location for the file.
If you have configured your log shipping on a single box between two different instances of SQL Server then you will get the above error.
If you configured log shipping on two different machines and the drive/path on which you have created your new database file on the primary server does not exist on the secondary server then this is the error you will get:
*** Error: The operating system returned the error ‘(The path specified does not exit or it is not accessible)’
Step 4
This above error is what we were getting through the alerts because someone had added a database file to the primary database without checking if the path existed on the secondary server. When you encounter this type of issue, first check the last restored file on the secondary database. We can get this info by running the below SQL statement or by running one of the built-in reports in SSMS. You can run the report by clicking on your instance name then right-click and choose reports then select ‘standard reports’ then run the ‘transaction logshipping status’ report. This will give you a detailed report about the health of all logshipping databases on that instance.
SELECT secondary_database,
last_restored_date,
last_restored_file
FROM dbo.log_shipping_secondary_databases
Step 5
Now you can easily find which log file you need to restore next. Once you have identified the exact log backup file which needs to be restored, we will verify whether the newly created database file is captured in this backup file or not. You can also find this info in the SQL Server error logs. Run the RESTORE FILELISTONLY statement to see the newly added database file.
RESTORE FILELISTONLY FROM DISK=’\\SERVERNAME\logshipping_secondary\Manvendra_20121125150255.trn’
Now you can see that your newly created file is captured in the backup file.
Step 6
Now go ahead and restore the correct sequence of log backups on the secondary server to bring logshipping databases in sync using the norecovery and move options as shown below..
RESTORE log Manvendra FROM Disk=’\\SERVERNAME\logshipping_secondary\Manvendra_20121125150255.trn’
with norecovery,
MOVE ‘Manvendra_test’
TO ‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Manvendra_test.ndf’
Step 7
Once you manually restored the backup successfully, run your logshipping restore job to restore all pending log backups if there were any. Now you can run Step 4 again to check the last restored file or run the logshipping status report to see the health of the database.
You have now fixed this issue and the new database file has been added to both databases.
Next Steps
- If you run into this issue, follow the above process to re-initialize logshipping and put both databases in sync
- Read more tips on SQL Server Log Shipping.
