Left and Write
Home MIDI Lyrics Bio Links Contact Guestbook
This Page Courtesy of: Left and Write.com

Roll Your Own SQL Log Shipping

Author:Brian Dill  
Created:2003-12-19
Updated:2003-12-20
Show Until:2004-12-31

Parts taken from the excellent article How to Perform Log Shipping at http://www.sql-server-performance.com/sql_server_log_shipping.asp. I have trimmed down the article in order to make it a quicker read, however the other article has more detailed explanations. I have also update some of the syntax to SQL 2000. For example the DBO_ONLY option in the RESTORE statement has been replaced with RESTRICTED_USER in SQL 2000.

Assumptions

An easy way to remember the names is this: "Luke takes TheForce from Hoth to Degoba". Luke (the SQL and SQLAgent account) takes TheForce (the database) from Hoth (primary server) to Degoba (secondary server).

The Setup

1) Create Shares

Create a folder on Hoth. D:\SqlLogShip
Share it out as \\Hoth\SqlLogShip

Create a folder on Degoba. D:\SqlLogShip
Share it out as \\Degoba\SqlLog Ship

Be sure that STARWARS\Luke can read/write to these shares.

2) Create backup devices on the primary server

USE master
EXEC sp_addumpdevice 'disk', 'TheForce_backup_device', 'D:\SqlLogShip\TheForce_backup_device.bak'
EXEC sp_addumpdevice 'disk', 'TheForce_log_backup_device', 'D:\SqlLogShip\TheForce_log_backup_device.bak'

3) Manually create a linked server from the primary to the secondary server using EM.

4) Create SP's on secondary server to restore db and logs

These SP's will be on the secondary server(s) and called remotely from the primary server.

The adm_kill_users kills all user processes on the secondary server so that the restore process won't get blocked out. The restore process needs excluseve access when restoring.
CREATE PROCEDURE adm_kill_users
@dbname SYSNAME
AS
-- Close all user connections to specified database.

--DECLARE @dbname SYSNAME                -- Name of database to shut down (Required)
--SELECT @dbname = 'TheForce'
BEGIN
  DECLARE @killcmd nvarchar(128)
  DECLARE @spid int
  DECLARE killcursor CURSOR FOR
      SELECT DISTINCT spid
      FROM master.dbo.sysprocesses
      WHERE dbid IN (SELECT dbid from master.dbo.sysdatabases WHERE name = @dbname) and spid >= 10
      -- Only select SPID > 10 to avoid killing any system processes

  OPEN killcursor
  FETCH killcursor into @spid
  WHILE @@FETCH_STATUS <> -1
    BEGIN
      SET @killcmd = 'kill ' + convert(varchar(10),@spid)
      PRINT @killcmd
      EXECUTE sp_executesql @killcmd
      FETCH killcursor into @spid
    END
  CLOSE killcursor
  DEALLOCATE killcursor
END
This SP does a full restore of the full backup. It will only be called once a day.
CREATE PROCEDURE adm_restore_database_backups

AS

RESTORE DATABASE TheForce
FROM DISK = 'D:\SqlLogShip\TheForce_backup_device.bak'
WITH
RESTRICTED_USER,
REPLACE,
STANDBY = 'D:\SqlLogShip\undo_TheForce.ldf',
MOVE 'TheForce_data' TO 'C:\SqlData\TheForce.mdf',
MOVE 'TheForce_Log' TO 'D:\SqlLog\TheForce_log.ldf'

WAITFOR DELAY '00:00:05'
--EXEC sp_dboption 'TheForce', 'single user', true
This SP restores the logs that are backed up. This will run as often as you want to "log ship" the logs from the primary server.
CREATE PROCEDURE adm_restore_log_backups

AS

RESTORE LOG TheForce
FROM DISK = 'D:\SqlLogShip\TheForce_log_backup_device.bak'
WITH
RESTRICTED_USER,
STANDBY = 'D:\SqlLogShip\undo_TheForce.ldf'

WAITFOR DELAY '00:00:05'

--EXEC sp_dboption 'TheForce', 'single user', true

5) Database backup Job

IDStep NameStep TypeOn SuccessOn Failure
1Truncate LogT-SQL ScriptGoto Next StepGoto Next Step
2Backup DatabaseT-SQL ScriptGoto Next StepQuit With Failure
3Copy BackupOS CommandGoto Next StepQuit With Failure
4Restore DatabaseT-SQL ScriptQuit With SuccessQuit With Failure

Step 1

Go ahead and truncate the log since we are about to do a full backup anyway
BACKUP LOG TheForce WITH TRUNCATE_ONLY
WAITFOR DELAY '00:00:05'

Step 2

"WITH INIT" forces restore over previous database.
BACKUP DATABASE TheForce TO TheForce_backup_device WITH INIT
WAITFOR DELAY '00:00:05'

Step 3

Copy from primary to secondary server. The "/Y" forces overwrite.
xcopy D:\SqlLogShip\TheForce_backup_device   \\Hoth\SqlLogShip\ /Y

Step 4

EXEC Hoth.master.dbo.adm_kill_users 'TheForce'
EXEC Hoth.master.dbo.adm_restore_database_backups

Step 5

Schedule the job to run once a day.

6) Log backup job

IDStep NameStep TypeOn SuccessOn Failure
1Backup LogT-SQL ScriptGoto Next StepQuit With Failure
2Copy LogOS Goto Next StepQuit With Failure
3Restore LogT-SQL ScriptQuit With SuccessQuit With Failure

Step 1

"WITH INIT" forces restore over previous log backups.
BACKUP LOG TheForce TO TheForce_log_backup_device WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'

Step 2

xcopy D:\SqlLogShip\TheForce_log_backup_device.bak   \\Hoth\SqlLogShip\ /Y

Step 3

EXEC Hoth.master.dbo.adm_kill_users 'TheForce'
EXEC Hoth.master.dbo.adm_restore_log_backups

Step 4

Schedule this job to run as often as you want to log ship. The more often you log ship, the more up-to-date your secondary server is, but it also adds extra burden on your primary server. It is basically a balance between maintaining previous levels of performance on your primary server and accepting work loss exposure. You could log ship every minute, but you would probably have users screaming at you because the database is slow all the time. I would suggest log shipping every 15-30 minutes. You have to make the call whether you need to bump it down to 5 minutes or up to 60 minutes.

Failover Steps

OK, the primary server failed. What do we do to get the secondary up and running like it is the primary?

Try to backup the log on the failed primary server.
BACKUP LOG TheForce TO TheForce_log_backup_device WITH INIT, NO_TRUNCATE
If you were able to backup, restore on secondary server.
RESTORE LOG TheForce
FROM DISK = 'D:\SqlLogShip\TheForce_log_backup_device.bak'
WITH
RESTRICTED_USER,
STANDBY = 'D:\SqlLogShip\undo_TheForce_log.ldf'
Finally, recover the secondary db, enabling it to act as primiary.
RESTORE DATABASE TheForce WITH RECOVERY
EXEC SP_DBOPTION 'TheForce', 'read only', 'false'
EXEC SP_DBOPTION 'TheForce', 'dbo use only', 'false'
Repoint your client applications to the secondary SQL server.


Talk Back

Thanks and More - Mike (2004-10-14 09:10:49)
Brian great explanation of log shipping.Clearer than the post you referred to . Question. How do you put the backup and moving scripts together so that there is no overlap mngong@yahoo.com

additions - MaxM (2004-06-05 17:17:17)
I had some trouble with overwriting existing files, so I extended this a bit. I put in a new job step in the log restore to rename the log file after restore. rename \\machine\share\DatabaseName_Log_Device.BAK DatabaseName_Log_%date:~10,4%-%date:~7,2%-%date:~4,2%.%time:~0,2%%time:~3,2%.BAK and then in the backup job (which essentially makes the previous logs irrelevant), I move the last backup and all logs to a subdir, (deleting the previous contents of that dir) and start the process over again. If you don't want to do this, you might just want to append /y to the xcopy command to force overwrite.


Name
Subject
Opinion
Home MIDI Lyrics Bio Links Contact Guestbook
Courtesy of: LeftAndWrite.com