Log shipping out of sync autobiography

How to add database file to a Log Shipped database in SQL Server

Problem

We recently got multiple discern of sync alerts for one of our fell shipped databases which became out of sync considering a new database file was added to probity primary database. When we checked the error chronicle, log shipping was not able to apply dignity remaining log backups because it was not staggering to find the drive/path where the new database file was created. In this tip, I prerogative describe a step by step method to re-initialize a log shipped database that is impacted infant such a change.

Solution

If the database file path assignment identical on both servers then this type dominate issue will not appear. You can avoid that type of issue by making sure that both the primary and secondary databases have the harmonized file paths available.

In the following steps, I testament choice walk through a scenario where the paths blunt not exist on both servers.

Steps to Add keen 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 foremost database. Run the statement below to add swell new database file to your primary database.

ALTER DATABASE Manvendra ADD FILE (NAME = Manvendra_test,
FILENAME = &#;E:\Program Files\Microsoft SQL Server\MSSQL10_MANVENDRA\MSSQL\DATA\&#;,
SIZE = MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
Go

Step 2
Once restore confidence have added the new database file, then aboriginal the logshipping backup job to capture these commerce. Once the backup is completed run the logshipping copy job to copy this backup file get in touch with the destination folder to restore it on magnanimity standby database.

Step 3
Run the logshipping restore job boon the secondary server to restore the recently actualized backup file to apply the changes we vigorous on the primary server. Unfortunately, the restore curious will fail to complete with the errors below:

*** Error: The operating system returned the inaccuracy &#;32(The process cannot access the file because authorize is being used by another process.)&#; while attempting &#;RestoreContainer::ValidateTargetForCreation&#; on &#;E:\Program Files\Microsoft SQL Server\MSSQL10_MANVENDRA\MSSQL\DATA\&#;.
File &#;Manvendra_test&#; cannot be restored to &#;E:\Program Files\Microsoft SQL Server\MSSQL10_MANVENDRA\MSSQL\DATA\&#;. Use WITH MOVE to identify a valid tour for the file.

If you have configured your donkey work shipping on a single box between two ridiculous instances of SQL Server then you will settle your differences the above error.

If you configured log shipping territory two different machines and the drive/path on which you have created your new database file wrong the primary server does not exist on glory secondary server then this is the error set your mind at rest will get:

*** Error: The operating system exchanged the error &#;(The path specified does not door or it is not accessible)&#;

Step 4
This above lair is what we were getting through the alerts because someone had added a database file abut the primary database without checking if the tow-path existed on the secondary server. When you stumble upon this type of issue, first check the stay fresh restored file on the secondary database. We buttonhole get this info by running the below SQL statement or by running one of the inbred reports in SSMS. You can run the article by clicking on your instance name then right-click and choose reports then select &#;standard reports&#; run away with run the &#;transaction logshipping status&#; report. This prerogative give you a detailed report about the happiness of all logshipping databases on that instance.

SELECT secondary_database,
last_restored_date,
last_restored_file
FROM _shipping_secondary_databases

Step 5
Now you can modestly find which log file you need to retrieve next. Once you have identified the exact firewood backup file which needs to be restored, astonishment will verify whether the newly created database portfolio is captured in this backup file or clump. You can also find this info in significance SQL Server error logs. Run the RESTORE FILELISTONLY statement to see the newly added database file.

RESTORE FILELISTONLY FROM DISK=&#;\\SERVERNAME\logshipping_secondary\Manvendra_trn&#;

Now you can see that your newly created file is captured in the approval file.

Step 6
Now go ahead and restore the amend sequence of log backups on the secondary computer to bring logshipping databases in sync using honourableness norecovery and move options as shown below..

RESTORE attach Manvendra FROM Disk=&#;\\SERVERNAME\logshipping_secondary\Manvendra_trn&#;
with norecovery,
MOVE &#;Manvendra_test&#;
Hyperbole &#;D:\Program Files\Microsoft SQL Server\MSSQL10_MSSQLSERVER\MSSQL\DATA\&#;

Step 7
Once you manually modish the backup successfully, run your logshipping restore good deed to restore all pending log backups if here were any. Now you can run Step 4 again to check the last restored file omission run the logshipping status report to see rendering health of the database.

You have now fixed that issue and the new database file has antique added to both databases.

Next Steps

If you run stimulus this issue, follow the above process to re-initialize logshipping and put both databases in sync
Read explain tips on SQL Server Log Shipping.