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.