Fix SQL Server Log Shipping After a New Database File has been Added

By:   |   Comments (8)   |   Related: > Log Shipping


I still use SQL Server Log Shipping to maintain a standby instance for some of my critical production databases.  Any time I add a new file to my primary SQL Server database, log shipping breaks because the drive layout on the standby server is different from the primary server.  How can I continue log shipping without a full database backup and restore?  Check out this tip to learn more.


The code in this tip will demonstrate how to continue SQL Server Log Shipping after a file has been added to the primary SQL Server database.  For more information on SQL Server Log Shipping, check out these tips.

SQL Server Log Shipping Prerequisites

While the code within this tip was developed and tested against a SQL Server 2005 instance, it should function on any version of SQL Server currently in use.  The scenario assumes that the AdventureWorks sample database has been attached.  SQL Server sample databases can be found on codeplex.


The paths used in the code may need to be updated to reflect the folder structure in use by other environments.

SQL Server Log Shipping Code

Drop the target database used by the RESTORE commands, if it already exists.

USE [master]

IF DB_ID('AdventureWorksCopy') IS NOT NULL
    IF DATABASEPROPERTYEX('AdventureWorksCopy', 'Status') <> 'ONLINE'
        RESTORE DATABASE [AdventureWorksCopy] WITH RECOVERY;

    DROP DATABASE [AdventureWorksCopy];

Issue the ALTER DATABASE command to ensure the primary database is in FULL recovery mode.  The primary database must be in FULL recovery mode before configuring SQL Server Log Shipping.

USE [master]


Generate a FULL backup of the primary database.

USE [master]

BACKUP DATABASE [AdventureWorks] TO DISK = 'c:\mssql\backup\yukon\FULL_AdventureWorks.bak' WITH CHECKSUM, FORMAT, INIT, STATS = 10;

Restore the standby database WITH NORECOVERY using the MOVE clause.  Restoring a FULL backup of the primary database is the first step in configuring log shipping.

USE [master]

RESTORE DATABASE [AdventureWorksCopy] FROM DISK = 'c:\mssql\backup\yukon\FULL_AdventureWorks.bak' WITH CHECKSUM, MOVE 'AdventureWorks_Data' TO 'c:\mssql\data\yukon\AdventureWorksCopy_Data.mdf', MOVE 'AdventureWorks_Log' TO 'c:\mssql\log\yukon\AdventureWorksCopy_Log.ldf', NORECOVERY, REPLACE, STATS = 10;

Generate a LOG backup of the primary database.

USE [master]

BACKUP LOG [AdventureWorks] TO DISK = 'c:\mssql\backup\yukon\LOG_AdventureWorks_1.bak' WITH CHECKSUM, FORMAT, INIT, STATS = 10;

Add a new data file to the primary database.

USE [master]

ALTER DATABASE [AdventureWorks] ADD FILE ( NAME = N'AdventureWorks_Data2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data2.ndf', SIZE = 2048KB, FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY];

Generate a second LOG backup of the primary database.

USE [master]

BACKUP LOG [AdventureWorks] TO DISK = 'c:\mssql\backup\yukon\LOG_AdventureWorks_2.bak' WITH CHECKSUM, FORMAT, INIT, STATS = 10;

Restore the first LOG backup to the standby database.

USE [master]

RESTORE LOG [AdventureWorksCopy] FROM DISK = 'c:\mssql\backup\yukon\LOG_AdventureWorks_1.bak' WITH CHECKSUM, NORECOVERY, STATS = 10;

Attempt to restore the second LOG backup to the standby database.

USE [master]

RESTORE LOG [AdventureWorksCopy] FROM DISK = 'c:\mssql\backup\yukon\LOG_AdventureWorks_2.bak' WITH CHECKSUM, NORECOVERY, STATS = 10;

The restore command fails with an error similar to the following, indicating the data file that was recently added to the primary database is still in use by that database.

Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data2.ndf' cannot be overwritten.  It is being used by database 'AdventureWorks'.
Msg 3156, Level 16, State 4, Line 1
File 'AdventureWorks_Data2' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data2.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Restore the second LOG backup to the standby database using the MOVE clause.

USE [master]

RESTORE LOG [AdventureWorksCopy] FROM DISK = 'c:\mssql\backup\yukon\LOG_AdventureWorks_2.bak' WITH CHECKSUM, MOVE 'AdventureWorks_Data2' TO 'c:\mssql\data\yukon\AdventureWorks_Data2.ndf', NORECOVERY, STATS = 10;

Review the file layout of both databases.

USE [master]

SELECT DB_NAME([database_id]) [database_name], [file_id], [type_desc] [file_type],
    [name] [logical_name], [physical_name]
FROM sys.[master_files]
WHERE [database_id] IN (DB_ID('AdventureWorks'), DB_ID('AdventureWorksCopy'))
ORDER BY [type], DB_NAME([database_id]);

Notice that both now contain the secondary data file that was added to the primary database.

database files


Just like the RESTORE DATABASE command, the MOVE clause can also be used with the RESTORE LOG command.  However, 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 third-party log backup file into native SQL Server backup format and then issue the native RESTORE LOG...WITH MOVE... command.

Next Steps
  • Be sure to verify RESTORE LOG...WITH MOVE functionality for any 3rd-party backup software used in your environment.
  • Check out other MSSQLTips related to SQL Server backup and restore operations as well as 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 Mike Eastland Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.

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

View all my tips

Comments For This Article

Tuesday, May 24, 2016 - 10:32:26 AM - Mike Back To Top (41550)

venkatakarthik - I can't speak directly to this error because I don't use the wizard to configure log shipping.  I've had much better luck with custom solutions.

That being said, you could check on the following:

1) Is the log backup job enabled and scheduled on the primary instance?
2) Is the backup job running?  If so, check the history for any errors.
3) Is the database you're trying to log ship running in FULL recovery mode?

Monday, May 23, 2016 - 9:25:49 AM - venkatakarthik Back To Top (41536)

 Hi Team,

SQL server log shipping has been configured suucessfully on 2008R2 server but my backup job has been failed. Please find the below error.

Mode: standby


The log shipping primary database MYINSWGGNSBCMP9.test9 has backup threshold of 60 minutes and has not performed a backup log operation for 811 minutes. Check agent log and logshipping monitor information.


Monday, February 24, 2014 - 3:24:08 AM - Thomas Mucha Back To Top (29549)

Great post. This is a problem I run into very often because I maintain a sliding partition window on a few of my logshipped servers mainly to save space. So I am always creating and removing files on the database.


I've written a script, with help of this post and others, that allows you to restore the failed logshipping quite easily. Please feel free to view my blog post and let me know what you think.


Thursday, January 23, 2014 - 2:46:55 AM - nagesh Back To Top (28185)



Thanks for your reply and i understood the article very well. Mike one more link regarding to the above post and this is just for your info not to comment you. Once again thanks for your support to us. Keep update us with more performance related articles.



It should workin SQL Server 2008 also as its the part of configuration. Its tested in SQL Server 2005 by me earlier only and its worked fine and my friend worked with SQL Server 2008 which was worked perfect. so please try again.

Wednesday, January 22, 2014 - 11:32:27 PM - manu Back To Top (28183)

Restore log with move statement fails in sql 2008, please share the version on which you tested it. As per my knowledge this works on sql 2012..

Wednesday, January 22, 2014 - 9:56:28 AM - Mike Back To Top (28175)

Hemant - Thanks for checking out the tip.


Nagesh - This tip is relevant for the following scenario:  You have log shipping configured between a primary and standby database.  You add a new data file to the primary and you want to create that same file on the secondary without reinitializing your log shipping configuration.  Hopefully, this answers your question.

Wednesday, January 22, 2014 - 9:18:23 AM - hemant Back To Top (28174)

Excellent, very helpful tip!!!

Wednesday, January 22, 2014 - 12:12:09 AM - nagesh Back To Top (28168)

Dear friend,

Can you please eloberate the article by specifying servers information(primary/secondary). It will help us to understand the above scenario.

Thanks in advance,


get free sql tips
agree to terms