How to add database file to a Existing Configured Log Shipping database in SQL Server with demo
How to add database file to a Existing Configured Log Shipping database in SQL Server
Now we can see primary Server information as see below
Primary Server Name: S Q L2012 ENTPEDT
Database Name: Test db E N T P EDT
you can see s y s.master_files path location is same in Primary server database
We can see Secondary Server details
Secondary Server Name: S Q L2012ENTPEDT
Secondary Database Name: Testdb E N TPTEST
we can see after restored database and configured log shipping data files relocated it is different in S y s.master_files and s p_help db as see below
Now we Need to add one file in Primary server as see below
You can see we are going to add one N D F file as See below
After added N D F file it is showing in both system and system S P as see below
Now we need to execute backup job to reflect these changes from Primary server to Secondary server as see below
Backup job went succeeded as see below
Now we need to execute copy job as see below in Secondary Server
in Secondary server copy job went succeeded as below
When you execute Restore job in Secondary server as see below
It throws Error as see below Because those file not exists on Secondary
To see Error log /History in job to know error cause
it gives error as those transaction log file could not able to restore from primary to secondary because those file is not available
So to get detail information just copy transaction log file from error log as see below
just open query window on Secondary and just paste it on Restore File List only command to know whether which transaction log backup capture the new L D F file or not
we can use Restore log with no recovery MOVE option but it will gives error as see below
RESTORE log db name FROM Disk='path location\db name_201211 25 15 02 55.t r n' with no recovery, MOVE 'new N D F name' TO 'destination file location\new N D F file name'
But below we have just give from s p _help db from source path location so it gives error as see below
We have to give original Destination path from S y s . master_files location with file name as see below then it will run successfully
after success restore DB went restoring mode
So we need bring db to read only/stand by mode so we need to go the below steps
choose the rest of the transaction log file as see below (Note: we can 't restore backup file when db in stand by /read only mode)
choose read only /stand by mode with rest of the transaction log file
It went success and db went read only/stand by mode as see below
Now we need to run restore job to get success any way '
we can see reports after fixed error
also see another example
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 1Add a new database file to the primary database. Run the statement below to add a new database file to your primary database.
Step 2Once 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 3Run 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:
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:
Step 4This 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.
Step 5Now 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.
Now you can see that your newly created file is captured in the backup file.
Step 6Now 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..
Step 7Once 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.