Simple Log-shipping of ‘Actions’ database from London to Paris using SQL2000 Std

SOURCE

All the code below is freely available on the internet. I first saw it in the SQL2000 SDK from where it has been improved many-many-times-over. Rather than rehash the implementation here is my handover notes.

OVERVIEW OF THIS PROJECT

Transaction-logs (Tlogs) from the London database ‘Actions’ are backed up every hour to a warm standby in Paris and restored there.

DETAILS

Hourly a SQL Job in London (‘Log ship Incidents to Paris’) executes a stored procedure (‘sp_ShipLogOnce’) that backs up the Tlog directly to Paris (to E:Backups shared as ‘Backup$’) then runs the Paris SQL Job (‘sp_ApplyStandByLog’) that restores it.

MAINTENANCE & LIMITATIONS

There is a Windows batch file on Paris (‘PurgeFilesExceptRecent.bat’) that will delete old Tlogs every 2am. It has been initially set to ignore the newest 720 (being one per hour for 30 days).

Logins on Paris need to be regularly updated from London (at least monthly). The stored procedure ‘sp_help_revlogins’ will script out the London logins, from which any new, sql, non-svc, entries should be added to Paris.

The Tlogs need to be restored in strict order. After a temporary network failure all outstanding Tlogs can be restored by running this command at Paris …

C:ApplyStandbyLogs.bat E:Data S:Backups

Alternatively individual Tlog can be restored using the stored procedure …

exec Sp_ApplyStandByLog
@DBName=’Actions’,
@BackupFileName=’S:BackupsActions[name.trn]’,
@UndoFile=’T:DATAActions_undo.ldf’,
@MoveFileTo=’S:BackupsActionsRestored’

SWITCHING ROLES

1. Disable the London LogShip job
2. Apply final Tlog (sp_ShipLogOnce with @LocalUndoFile)
3. Change Paris ‘Actions’ database to read/write, multi-user
4. Enable the Paris LogShip job

DISASTER RECOVERY

If London is unavailable, only step-3 above is needed. IE:-

exec sp_dboption 'actions', 'dbo use only', 'false'
go
exec sp_dboption 'actions', 'read only', 'false'
go
restore database actions with recovery
go