Implementing Log Shipping for Many Databases
In my previous post, I discussed using log shipping as a method for migrating databases to reduce application downtime. Using Management Studio to configure log shipping for each database is fine… until you have a large number of databases to implement. Here I will describe some sample scripts that will utilize a reference table and implement log shipping for multiple databases. I am sure there are other solutions that may fit your environment/situation better than this one, but I have had good success with this method. It has been used with the source and destination both being SQL 2005 as well as the with the destination being SQL 2008 and SQL 2008 R2.
This is a fairly simple approach where I mostly just scripted out a single implementation of log shipping using Management Studio. I then collected variable data in a table and wrapped a cursor around the procedure calls required to set up log shipping. These scripts assume that all folder structures are pre-created.
The first thing to do is set up a reference table that will hold some variables required for each database. In many cases these variables may be the same for all databases, but I wanted a little extra flexibility. I created the following table in msdb. This table must exist on both the primary and secondary instance and should contain identical data.
CREATE TABLE LSDBList
(
[database] nvarchar(255) NOT NULL,
backup_directory nvarchar(255) NOT NULL,
backup_share nvarchar(255) NOT NULL,
backup_destination_directory nvarchar(255) NOT NULL,
start_time_offset int NOT NULL
)
These columns directly correlate with inputs required for los shipping setup.
-
- backup_directory = local path for backing up the transaction logs
- backup_share = UNC path to the same directory listed above. This is used by the secondary server to copy the tran log backup files.
- backup_destination_directory = local path on the secondary server for the shipped tran log backups
-
- start_time_offset = this is used to stagger the job execution so not all jobs kick off at once. It is best to spread these out over a period of time for your databases.It is an integer value in minutes, so 10 minutes after the hour would be 10.
For my sample, I used 3 copies of the AdventureWorks database. I named them AdventureWorks1, AdventureWorks2, and AdventureWorks3. I then populated the table with data specific to my databases and test environment. Don’t forget to create and populate the table on both the primary and secondary instance.
INSERT INTO LSDBList ([database],backup_directory, backup_share,
backup_destination_directory, start_time_offset)
VALUES
(N'AdventureWorks1', N'c:\Backup\TranLogBak\AdventureWorks1' ,
N'\\LSPRIMARY\C$\Backup\TranLogBak\AdventureWorks1' ,
N'c:\ShippedTranLogs\AdventureWorks1', 0)
INSERT INTO LSDBList ([database],backup_directory, backup_share,
backup_destination_directory,start_time_offset)
VALUES
(N'AdventureWorks2',N'c:\Backup\TranLogBak\AdventureWorks2' ,
N'\\LSPRIMARY\C$\Backup\TranLogBak\AdventureWorks2' ,
N'c:\ShippedTranLogs\AdventureWorks2', 0)
INSERT INTO LSDBList ([database],backup_directory, backup_share,
backup_destination_directory, start_time_offset)
VALUES
(N'AdventureWorks3',N'c:\Backup\TranLogBak\AdventureWorks3' ,
N'\\LSPRIMARY\C$\Backup\TranLogBak\AdventureWorks3' ,
N'c:\ShippedTranLogs\AdventureWorks3', 0)
Now that I have metadata about the databases I am setting up for log shipping, I need to take a full backup on the primary and then restore the full backup WITH NORECOVERY on the secondary. The databases must be in full recovery mode at this time. Here are 2 scripts that can be used to generate the backup and restore commands for all your databases. Note that the restore script will be run against your primary instance to generate restore commands for your secondary instance. You will need to
Backup Script:
SELECT
'Backup Database [' + [database] + '] to disk = ''c:\Backups\'
+ [database] + '_LSINIT_FULL.bak'' WITH INIT'
FROM
[msdb].[dbo].[LSDBList]
Restore Script
SET NOCOUNT ON
-- This script should be run on the primary to generate a restore command for each database.
-- The restore commands that are generated should then be run against the primary after the
-- full backup files have been copied to the secondary server
DECLARE
@dbid int
,@dbname varchar(1000)
,@dbfile varchar(4000)
,@dbfileonly varchar(4000)
,@dbfilename varchar(4000)
,@sqlrestore varchar(4000)
,@dbfiledest varchar(4000)
,@logfiledest varchar(4000)
,@dbfilelocal varchar(4000)
,@logfilelocal varchar(4000)
,@backup_path varchar(4000)
-- Set data and log file destinations --
Select
@backup_path = 'C:\backup' -- Path to the full backup <-- EDIT REQUIRED
,@dbfilelocal = 'C:\MSSQL\Data' -- Data file location on the secondary <-- EDIT REQUIRED
,@logfilelocal = 'C:\MSSQL\Log' -- Log file location on the secondary <-- EDIT REQUIRED
DECLARE cRestore CURSOR
READ_ONLY
FOR
SELECT
S.[DATABASE_ID]
,S.[name]
FROM
SYS.Databases S
Inner Join dbo.LSDBList LDB
On S.[name] = LDB.[database]
OPEN cRestore
FETCH NEXT FROM cRestore INTO @dbid, @dbname
WHILE (@@fetch_status <> -1)
BEGIN
-- add data files to restore script
DECLARE cDBFiles CURSOR
READ_ONLY
FOR SELECT SF.[name]
,COALESCE(RIGHT([filename], NullIf(CHARINDEX(REVERSE('\'), REVERSE([filename])), 0)-1),
[filename])as fileonly
FROM
SYS.Databases S
INNER JOIN dbo.LSDBList LDB
ON S.[name] = LDB.[database]
INNER JOIN sys.sysaltfiles SF
ON S.[database_id] = SF.[dbid]
WHERE SF.groupid <>0 1 and SF.dbid = @dbid
open cDBFiles
FETCH NEXT FROM cDBFiles INTO @dbfilename,@dbfileonly
SET @sqlrestore = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = '''
+ @backup_path + '\' + @dbname + '_LSINIT_FULL.bak'' WITH '
WHILE (@@fetch_status <>-1 )
BEGIN
-- Add file to restore script
SET @sqlrestore = @sqlrestore + 'MOVE ''' + @dbfilename + ''' to '''
+ @dbfilelocal + '\' + @dbfileonly + ''''
FETCH NEXT FROM cDBFiles INTO @dbfilename,@dbfileonly
IF (@@fetch_status <> -1)
SET @sqlrestore = @sqlrestore + ', '
Else
SET @sqlrestore = @sqlrestore + ', '
END
close cDBFiles
deallocate cDBFiles
-- add log files to restore script
DECLARE cDBFiles CURSOR
READ_ONLY
FOR SELECT SF.[name]
,COALESCE(RIGHT([filename], NullIf(CHARINDEX(REVERSE('\'), REVERSE([filename])), 0)-1),
[filename])as fileonly
FROM
SYS.Databases S
INNER JOIN dbo.LSDBList LDB
ON S.[name] = LDB.[database]
INNER JOIN sys.sysaltfiles SF
ON S.[database_id] = SF.[dbid]
WHERE SF.groupid = 0 and SF.dbid = @dbid
open cDBFiles
FETCH NEXT FROM cDBFiles INTO @dbfilename,@dbfileonly
WHILE (@@fetch_status <> -1)
BEGIN
-- Add file to restore script
SET @sqlrestore = @sqlrestore + 'MOVE ''' + @dbfilename + ''' to '''
+ @logfilelocal + '\' + @dbfileonly + ''''
FETCH NEXT FROM cDBFiles INTO @dbfilename,@dbfileonly
IF (@@fetch_status <> -1)
SET @sqlrestore = @sqlrestore + ', '
Else
SET @sqlrestore = @sqlrestore + ' '
END
close cDBFiles
deallocate cDBFiles
-- Add NORECOVERY
SET @sqlrestore = @sqlrestore + ',NORECOVERY; '
-- output restore script
print '-- Database: ' + @dbname
-- SELECT @sqlrestore
FETCH NEXT FROM cRestore INTO @dbid, @dbname
END
CLOSE cRestore
DEALLOCATE cRestore
GO
Now that you have your primary databases restored (and still in recovery mode) on your secondary instance, you can now set up log shipping. This first sample script should be run on the primary instance. Note this is assuming the previous steps were completed successfully, and both the primary and secondary instance can connect to one another over SQL as well as SMB. As a good practice, I always validate both of these manually first.
-- Global script variables required
DECLARE @primary_server nvarchar(255)
DECLARE @secondary_server nvarchar(255)
-- Cursor level variables required
DECLARE @database nvarchar(255)
DECLARE @backup_directory nvarchar(255)
DECLARE @backup_share nvarchar(255)
DECLARE @backup_destination_directory nvarchar(255)
DECLARE @start_time_offset int
SET @primary_server = 'LSPRIMARY' -- <--put yout value here
SET @secondary_server = 'LSSECONDARY' -- <--put your value here
-- Execute the following statements at the Primary to configure Log Shipping
-- for the database @primary_server.@database,
-- The script needs to be run at the Primary in the context of the [msdb] database.
-- -----------------------------------------------------------------------------------
-- Adding the Log Shipping configuration
DECLARE db_cursor CURSOR FOR
SELECT [database],backup_directory, backup_share,
backup_destination_directory, start_time_offset
FROM LSDBList
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO
@database, @backup_directory, @backup_share,
@backup_destination_directory, @start_time_offset
WHILE @@Fetch_Status = 0
BEGIN
DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
DECLARE @SP_Add_RetCode As int
DECLARE @backup_job_name nvarchar(255)
DECLARE @backup_schedule_name nvarchar(255)
DECLARE @copy_job_name nvarchar(255)
DECLARE @copy_schedule_name nvarchar(255)
DECLARE @restore_job_name nvarchar(255)
DECLARE @restore_schedule_name nvarchar(255)
SET @backup_job_name = N'LSBackup_' + @database
SET @backup_schedule_name = N'LSBackupSchedule_' + @database
SET @copy_job_name = N'LSCopy_' + @primary_server + @database
SET @copy_schedule_name = N'LSCopySchedule_' + @primary_server + @database
SET @restore_job_name = N'LSRestore_' + @primary_server + @database
SET @restore_schedule_name = 'LSRestore_Schedule_' + @primary_server + @database
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = @database
,@backup_directory = @backup_directory
,@backup_share = @backup_share
,@backup_job_name = @backup_job_name
,@backup_retention_period = 4320
,@backup_threshold = 180
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN
DECLARE @LS_BackUpScheduleUID As uniqueidentifier
DECLARE @LS_BackUpScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name = @backup_schedule_name
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 8
,@freq_subday_interval = 1
,@freq_recurrence_factor = 0
,@active_start_date = 20110721
,@active_end_date = 99991231
,@active_start_time = @start_time_offset
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID
EXEC msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1
END
EXEC master.dbo.sp_add_log_shipping_alert_job
EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = @database
,@secondary_server = @secondary_server
,@secondary_database = @database
,@overwrite = 1
FETCH NEXT FROM db_cursor INTO
@database, @backup_directory, @backup_share,
@backup_destination_directory, @start_time_offset
SET @LS_BackupJobId = NULL
SET @LS_PrimaryId = NULL
SET @LS_BackUpScheduleUID = NULL
SET @LS_BackUpScheduleID = NULL
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
You will now see transaction log backup jobs on your primary instance. They are set up so that transaction log backups will occur once an hour. Now you can run the last sample script on the secondary in order to complete setting up log shipping.
-- Global script variables required
DECLARE @primary_server nvarchar(255)
DECLARE @secondary_server nvarchar(255)
-- Cursor level variables required
DECLARE @database nvarchar(255)
DECLARE @backup_directory nvarchar(255)
DECLARE @backup_share nvarchar(255)
DECLARE @backup_destination_directory nvarchar(255)
DECLARE @start_time_offset int
SET @primary_server = 'LSPRIMARY' -- <--put yout value here
SET @secondary_server = 'LSSECONDARY' -- <--put your value here
-- Execute the following statements at the Secondary to configure Log Shipping
-- for the database @secondary_server.@database,
-- the script needs to be run at the Secondary in the context of the [msdb] database.
-------------------------------------------------------------------------------------
-- Adding the Log Shipping configuration
-- ****** Begin: Script to be run at Secondary: ******
DECLARE db_cursor CURSOR FOR
SELECT [database],backup_directory, backup_share,
backup_destination_directory, start_time_offset
FROM LSDBList
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO
@database, @backup_directory, @backup_share,
@backup_destination_directory, @start_time_offset
WHILE @@Fetch_Status = 0
BEGIN
DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
DECLARE @LS_Add_RetCode As int
DECLARE @backup_job_name nvarchar(255)
DECLARE @backup_schedule_name nvarchar(255)
DECLARE @copy_job_name nvarchar(255)
DECLARE @copy_schedule_name nvarchar(255)
DECLARE @restore_job_name nvarchar(255)
DECLARE @restore_schedule_name nvarchar(255)
SET @backup_job_name = N'LSBackup_' + @database
SET @backup_schedule_name = N'LSBackupSchedule_' + @database
SET @copy_job_name = N'LSCopy_' + @primary_server + @database
SET @copy_schedule_name = N'LSCopySchedule_' + @primary_server + @database
SET @restore_job_name = N'LSRestore_' + @primary_server + @database
SET @restore_schedule_name = 'LSRestore_Schedule_' + @primary_server + @database
EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = @primary_server
,@primary_database = @database
,@backup_source_directory = @backup_share
,@backup_destination_directory = @backup_destination_directory
,@copy_job_name = @copy_job_name
,@restore_job_name = @restore_job_name
,@file_retention_period = 4320
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryCopyJobScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =@copy_schedule_name
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20110721
,@active_end_date = 99991231
,@active_start_time = @start_time_offset
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__CopyJobId
,@schedule_id = @LS_SecondaryCopyJobScheduleID
DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryRestoreJobScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =@restore_schedule_name
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20110721
,@active_end_date = 99991231
,@active_start_time = @start_time_offset
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__RestoreJobId
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
END
DECLARE @LS_Add_RetCode2 As int
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = @database
,@primary_server = @primary_server
,@primary_database = @database
,@restore_delay = 0
,@restore_mode = 0
,@disconnect_users = 0
,@restore_threshold = 180
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@overwrite = 1
END
IF (@@error = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__CopyJobId
,@enabled = 1
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__RestoreJobId
,@enabled = 1
END
FETCH NEXT FROM db_cursor INTO
@database, @backup_directory, @backup_share,
@backup_destination_directory, @start_time_offset
SET @LS_Secondary__CopyJobId = NULL
SET @LS_Secondary__RestoreJobId = NULL
SET @LS_Secondary__SecondaryId = NULL
SET @LS_SecondaryCopyJobScheduleUID = NULL
SET @LS_SecondaryCopyJobScheduleID = NULL
SET @LS_SecondaryRestoreJobScheduleUID = NULL
SET @LS_SecondaryRestoreJobScheduleID = NULL
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
-- ****** End: Script to be run at Secondary: ******
It may seem like a lot of scripts, but it is not extremely complicated and is far less agonizing than going through the log shipping wizard hundreds of times! Test it out and enjoy.
Comments
Anonymous
October 31, 2011
Lisa thank you for documenting this approach. I am using your approach to migrate from my current Sql 2005 environment to Sql 2012 and you have simplified my effort. Thanks again!Anonymous
October 05, 2015
Hello! Your post is excellent! I'm new in the area of SQL Server and I'm certainly on the part of the Backup and Restore Scripts because I performed in the first instance where the databases are up and they were not created in the second instance, where they will stay. Could you help me please, indicating which parts of these scripts are customizable?Anonymous
December 24, 2015
This is great, I have to set up log shipping for 200+ databases and this makes it so easy!Anonymous
April 12, 2016
Never look a gift horse in the month. Having said that, there are several problems with these scripts, ranging from syntax errors to misunderstanding the @active_start_time parameter of sp_add_schedule. Other than commenting here, what's the best way to send in corrections and get this otherwise useful solution improved/fixed?- Anonymous
April 14, 2016
Hi Mark - This blog post is quite old and the scripts were tested/used against a SQL 2005 source/destination and SQL 2005 source and SQL 2008 destination. The article stipulates that. The syntax for many of the system procs has changed with various releases. Thanks for taking the time to comment. I may update the article to specify that at the beginning.
- Anonymous