By: Mike Eastland | Comments (8) | Related: > 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.
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.
GO
ALTER DATABASE [AdventureWorks] SET RECOVERY FULL;
Generate a FULL backup of the primary database.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
About the author
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
