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

By:   |   Updated: 2012-12-10   |   Comments (12)   |   Related: 1 | 2 | 3 | 4 | > Log Shipping


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

Add a database file to primary db

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

Find our last restored file

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'

Restore filelistonly to verify newly created file is added in backup or not

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'

Manually Restore log backup

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-12-10

Comments For This Article




Wednesday, April 22, 2015 - 11:37:13 PM - Madhu Back To Top (37001)

Clear Steps

Monday, January 26, 2015 - 8:50:55 AM - ganesh Back To Top (36054)

 

 

can you please tell me to how to add a share folder to my database in log shipping 

 

Wednesday, June 4, 2014 - 6:38:12 AM - Ganesan N Back To Top (32073)

Thanks for the solution. I had the same issue and It saved a lot of time for me. 

I have one clarification, it worked though we have added the new NDF file in a different file group in Primary server. Does the file group gets created automatically while restoring the same log , explicitly specifying the MOVE ?

Thank you.

 

 

Monday, February 24, 2014 - 3:27:07 AM - Thomas Mucha Back To Top (29550)

Manvendra,

 

This post was a life saver for me. Using your help I created a stored procedure to make fixing this error really easy. Please have a look at my blog post: http://myhumblesqltips.blogspot.co.uk/2014/01/every-month-i-need-to-maintain-various.html

I'd love toknow what you think.

 

Tom

Monday, December 23, 2013 - 1:52:35 PM - VINAY Back To Top (27872)

hi, Mavendra

     Thanks for sharing the Valueable information. I add a ndf file to the Primary server , the restore job is fialed. Now i follow the steps as you given above. Then the Restore job is running fine. BUt the Copy job is filed. Could you please guide me.

 

 

Thnaks in advance...

Thursday, November 14, 2013 - 11:30:18 AM - Mike Back To Top (27497)

Eric - Once the standby database has the same number and type of files as the primary, the subsequent log backups can be restored successfully.

That being said, be sure to thoroughly test this approach if you are using a third-party backup utility. The first time I ran into this scenario where it was not feasible to restore from a FULL backup, I discovered the backup utility I was using did not support the MOVE clause with its RESTORE LOG equivalent. I was forced to convert the log backup file into native backup format and then issue the native RESTORE LOG...WITH MOVE... command.

Wednesday, November 6, 2013 - 10:16:31 AM - Eric Back To Top (27423)

Maybe I'm missing the obvious here, but how would restoring one file with MOVE prevent all of the following .trn files from erroring during restore?

Thursday, October 24, 2013 - 1:41:36 AM - Indrajeet Mashale Back To Top (27250)

Realy helpful for me.

Friday, January 18, 2013 - 7:47:09 AM - Azeez Back To Top (21551)

Thanks for your update on the process.

If my destination LS is having different path how do we achive this from Point 6

Wednesday, December 12, 2012 - 2:29:25 PM - Bill Back To Top (20921)

Thank you!  I actually had this problem earlier this week.  This is probably fairly common if the primary is on an instance in an Active/Active cluster.

Monday, December 10, 2012 - 9:07:10 PM - Mark Back To Top (20857)

As always, one more fantastic article for DBAs by the Author!!!!!!!! Great job Sir!! Can you write up more on troubleshooting Performace issues? 

Monday, December 10, 2012 - 4:51:23 AM - rajendra Back To Top (20848)

Can we apply the same process for Log shipping database that are in Stand By Mode or what changes we needs to have?














get free sql tips
agree to terms