Simple SQL Server Log Shipping

Log shipping allows one to maintain a standby copy of a database that is automatically updated from the source database every so often (i.e. every 15 minutes).  This standby copy is read-only and can be used for reporting or backups.  In addition, in the event of failure of the server housing the source database, you have an almost up to date copy elsewhere.

SQL Server 2000 Enterprise Edition has some built-in log shipping functionality as does SQL Server 2005.  However, those solutions tend to be fairly complicated with a lot of moving parts.  Log shipping ultimately is fairly simple.  You backup a log on the source server/instance and apply/restore it in standby mode on the destination sever/instance.

SQL Server 2000 Resource Kit included some 'Simple Log Shipping' stored procedures.  Those procedures were not that robust when it came to applying the log, plus for whatever reason the default log shipping methodology was to schedule a daily stored procedure that would actually spin in a loop and every so often perform a log ship until the end of the day. This is silly because any error aborts the whole thing until the next day. The SQL Server Agent has a perfectly good recurring scheduler, so it's better to use it with a 'single-run' version.

Here we present a complete multi-database log shipping solution for SQL Server 2000 based on improved versions of the Resource Kit stored procedures.  It will likely work with SQL 2005 as well but this is untested.  It consists of three stored procedures and two batch files which are optional but helpful.  We will refer to the source server as having the name SRC and destination server as DEST.

Environment:
  • On both SRC and DEST, we have C:\MSSQL\DATA and D:\MSSQL\DATA directories.  On DEST, we have E:\DBBackups directory, which is also shared as DBBackups (this is where the logs will be shipped to and stored for some period of time, so it should have a fair amount of available disk space).  On SRC, we have C:\MSSQL\LOG directory.  These specific paths are not required, nor is having multiple drives but we'll use these here.

  • Each database on SRC that will have its logs shipped is configured for Full recovery mode, has its .mdf database files stored in D:\MSSQL\DATA .ldf log files stored in C:\MSSQL\DATA and is NOT part of any maintenance plan that backs up and truncates logs (regular db backups are OK).

  • On SRC, SQL Server and SQL Server Agent services are set to Automatic (and started) and to login as a user with sufficient rights to write to \\DEST\DBBackups and to invoke a stored procedure in master database on DEST.

Initial Setup:
  1. On SRC, set up the destination SQL Server instance on DEST as a linked server named DEST (using the same name is not required).

  2. Also on SRC, in Enterprise Manager, under Management \ SQL Server Agent \ Operators, create an Operator to receive alerts if something goes wrong, for example, name it SRC and configure it to receive Net Send (on-screen) alerts.

  3. On DEST, you'll need to manually recreate any SQL users you may have on SRC.

  4. Create the three stored procedures in the master database on each server by executing the three .sql files against master in Query Analyzer (make sure you have a dbo-equivalent connection (i.e. 'sa' or Administrator))

  5. Place the two .bat batch files on DEST, let's say in C:\

  6. On DEST, schedule PurgeFilesExceptRecent.bat to delete log files older than your desired retention period.  If, for example, you wanted to ship logs every 15 minutes (i.e. 4 times an hour) and keep approximately a month's worth of logs, you will want to retain 3000 files for each database. On Windows 2003, you can schedule the batch file to run daily at 2 am with the following command line (all one line):

    schtasks /Create /RU SYSTEM /SC DAILY /ST 02:00 /TN PurgeFilesExceptRecent
     /TR "C:\PurgeFilesExceptRecent.bat 3000 E:\DBBackups"

    (If you wanted to retain a different number of files for one group of database from the rest, you would need to log ship them to a different base directory, i.e. E:\DBBackups2)

    For more information on this batch file, execute PurgeFilesExceptRecent.bat /?

Steps required to set up each database for log shipping:
  1. Let's say the database is named MyDb.  Backup the database on SRC to a file accessible from DEST, let's say you back it up to a file E:\MyDb.bak on DEST.

  2. On DEST, restore the database in standby mode by executing the following in Query Analyzer:

    RESTORE DATABASE MyDb
       FROM DISK='E:\MyDb.bak'
       WITH STANDBY = 'C:\MSSQL\DATA\MyDb_undo.ldf'

    (Database Name with _undo.ldf suffix above is required)

  3. Create the following directory on DEST: E:\DBBackups\MyDb\restored

  4. On SRC, execute the following in Query Analyzer against master (make sure you have a dbo-equivalent connection (i.e. 'sa' or Administrator)):

    sp_CreateLogShipJob
    @DBName = 'MyDb', -- Name of the database to ship logs for
    @StandbyServer = 'DEST', -- Standby server to receive/apply the logs
    @BackupLocationPath = '\\DEST\DBBackups', -- UNC path to place log backup files WITHOUT the database-specific directory
    @UndoFilePath = 'C:\MSSQL\DATA', -- Local path on DEST to the undo file location
    @RestoreLocationPath = 'E:\DBBackups', -- Local path on DEST to the log backup files WITHOUT the database-specific directory
    @FrequencyMinutes = 15, -- Log shipping frequency in minutes
    @OnErrorNetSendTo = 'SRC', -- Net Send recipient to which to send on-screen notifications of errors (must already exist)
    @LogFile = 'C:\MSSQL\LOG\LogShip.log' -- Text file on SRC to which the job will append notices (both success and error)

Operation details:

The last step above creates a SQL Server Agent Job that runs every 15 minutes and ships the log for MyDb to the standby server.  The log is backed up directly to \\DEST\DBBackups\MyDb (the backup file has a .TRN extension and a naming scheme that matches the default naming scheme of SQL Server Maintenance plans that backup logs). Provided that was successful, MyDb log is truncated on SRC.  On DEST, sp_ApplyStandByLog is then invoked, which puts the standby database into single-user mode, applies/restores the log, moves the .TRN file into \\DEST\DBBackups\MyDb\restored, waits one second, and then puts the database back into multi-user mode.

The job is configured to retry once after a one-minute delay on any error, to append notices and errors to C:\MSSQL\LOG\LogShip.log on SRC, to Net Send errors to SRC (i.e. on-screen notifications) and to append output to step history.  Note that to see this detailed output you have to check 'Show step details' when you go to view Job History.

Although you cannot configure the above settings with sp_CreateLogShipJob, you can change the job settings in Enterprise Manager.  You can also create the job manually which will also give you more flexibility with the naming and arrangement of files and directories on DEST.  This is what your job would need to execute:

exec sp_ShipLogOnce
@DBName='MyDb', -- Name of the database to ship logs
@StandbyServer='DEST', -- Standby server to receive/apply the logs
@BackupLocationPath='\\DEST\DBBackups\MyDb', -- UNC path to place log backup files
@UndoFile='C:\MSSQL\DATA\MyDb_undo.ldf', -- Undo file on the standby server
@RestoreLocationPath='E:\DBBackups\MyDb' -- Optional local path to the log backup files on the standby server

Troubleshooting:

The most common problem with log shipping is logs must be applied strictly in order.  What typically happens is the log is backed up to \\DEST successfully and then truncated on SRC but then it fails to apply because SQL Server on DEST is down, for example.  So, in 15 minutes SRC makes another backup and even though SQL Server is now up on DEST, it can't restore it because it missed the previous file.

First of all, you can manually apply a log backup file on DEST in Query Analyzer:

sp_ApplyStandByLog
@DBName = 'MyDb',
@BackupFileName = 'E:\DBBackups\MyDb\MyDb_tlog_200707201345.TRN',
@UndoFile = 'C:\MSSQL\DATA\MyDb_undo.ldf',
@MoveFileTo = 'E:\DBBackups\MyDb\restored'

More importantly, on DEST, you can execute the ApplyStandByLogs.bat batch file to automatically 'catch up' on any missed restores for one or all databases:

ApplyStandByLogs.bat C:\MSSQL\DATA E:\DBBackups

For more information on this batch fie, execute ApplyStandByLogs.bat /?

Switching source / destination roles:

We don't have a handy batch file or stored proc to do this yet but essentially, you'd need to do the something like the following for each database:

-- get the log shipping job id:
SELECT @job_id = job_id
  FROM msdb.dbo.sysjobs_view
 WHERE db_name LIKE 'Log ship '+@DBName+'%'
-- disable the transaction log dump:
EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0
-- make final dump and switch roles:
-- execute sp_ShipLogOnce with @LocalUndoFile

 

© Copyright 1996-2010 by am.net and Solitex Networks. Legal Notices.
Creative Commons License Articles on this site are licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.