Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (12)   |   Related Tips: 1 | 2 | 3 | 4 | More > Log Shipping

Attend these FREE SQL Server 2017 webcasts >> click to register


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.


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Clear Steps


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

 

 

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

 


Wednesday, June 04, 2014 - 6:38:12 AM - Ganesan N Back To Top

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

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

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

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 06, 2013 - 10:16:31 AM - Eric Back To Top

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

Realy helpful for me.


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

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

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

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

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


Learn more about SQL Server tools