Learn more about SQL Server tools

   
   















































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

MSSQLTips author Mike Eastland By:   |   Read Comments (6)   |   Related Tips: More > Log Shipping
Problem

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.

Solution

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.

Disclaimer

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]
GO

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

    ALTER DATABASE [AdventureWorksCopy] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [AdventureWorksCopy];
END

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]
GO

ALTER DATABASE [AdventureWorks] SET RECOVERY FULL;

Generate a FULL backup of the primary database.

USE [master]
GO

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]
GO

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]
GO

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]
GO

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]
GO

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]
GO

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]
GO

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]
GO

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]
GO

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_name file_id file_type logical_name physical_name
AdventureWorks 1 ROWS AdventureWorks_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Data.mdf
AdventureWorks 2 LOG AdventureWorks_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Log.ldf
AdventureWorks 3 ROWS AdventureWorks_Data2 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Data2.ndf
AdventureWorksCopy 1 ROWS AdventureWorks_Data c:\mssql\data\yukon\AdventureWorksCopy_Data.mdf
AdventureWorksCopy 2 LOG AdventureWorks_Log c:\mssql\log\yukon\AdventureWorksCopy_Log.ldf
AdventureWorksCopy 3 ROWS AdventureWorks_Data2 c:\mssql\data\yukon\AdventureWorksCopy_Data2.ndf

Conclusion

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.


Last Update: 1/22/2014


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.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, February 24, 2014 - 3:24:08 AM - Thomas Mucha Read The Tip

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. 

http://myhumblesqltips.blogspot.co.uk/2014/01/every-month-i-need-to-maintain-various.html

 


Thursday, January 23, 2014 - 2:46:55 AM - nagesh Read The Tip

Mike,

 

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.

http://www.mssqltips.com/sqlservertip/2824/how-to-add-database-file-to-a-log-shipped-database-in-sql-server/

 

Manu,

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 Read The Tip

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 Read The Tip

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 Read The Tip

Excellent, very helpful tip!!!


Wednesday, January 22, 2014 - 12:12:09 AM - nagesh Read The Tip

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,

Nagesh




 
Sponsor Information