home
Google



Log Shipping
Author Nigel Rivett

This is simply backing up the logs from one database and restoring to another.
To do this you need a full backup and the sequence of log backups following it.
You must have all log backups. i.e. there cannot be another task backing up logs to another place.
The log must also never be truncated.
The database must use the full or bulk logged recovery model.
The full backup is restored and the database left in standby mode (able to restore more logs).
The logs are then each restored in sequence - also leaving the database in standby mode.


This process is based around the back procedures on this site.
The backup names will have the format

_Full_yyyymmdd_hhmmss.bak
_Log_yyyymmdd_hhmmss.bak

These are taken to different folders on the source server.
On the destination server a job is run to copy all log files.
Another job (or task in the same job) then applies all logs in order.

Process
This is to apply the process to svr1.mydb log shipping to svr2.mydb



Source server

Create an Admin database.
use Admin
Create table DatabaseBackup
	(
	Name varchar(128) primary key nonclustered ,
	BackupFlagFull varchar(1) not null check (BackupFlagFull in ('Y','N')) ,
	BackupFlagLog varchar(1) not null check (BackupFlagLog in ('Y','N')) ,
	RetentionPeriodFull datetime not null ,
	RetentionPeriodLog datetime not null
	)

Create the SP Admin..s_BackupAllDatabases found at http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html.
Create a folders
C:\Backups\Full\
C:\Backups\Log\
Create jobs to backup the database 

Full backup scheduled daily
s_BackupAllDatabases 'C:\Backups\Full\', 'Full'
log backup scheduled every 15 mins
s_BackupAllDatabases 'C:\Backups\Log\', 'Log'

You can use the table DatabaseBackup to prevent databases from being backed up and to set the retension period of the backup files.


Distination server

Create folders
C:\BackupCopy\Full\
C:\BackupCopy\Log\
C:\BackupCopy\Log\Archive\
C:\sql\data\
C:\sql\Log\

Create an Admin database
use Admin
create the stored procedures
s_nrSyncDir        http://www.mindsdoor.net/SQLAdmin/s_nrSyncDir.html
s_RestoreDatabase  http://www.mindsdoor.net/SQLAdmin/s_RestoreDatabase.html

Create a job to copy all backups from the source server
Schedule every 5 mins?
exec s_nrSyncDir
	@LocalDir1 = 'c:\BackupCopy\MyServer\Full\' ,
	@LocalDir2 = null ,
	@RemoteDir = '\\svr1\c$\Backup\Full\' ,
	@FileMask = '*.bak' ,
	@RetainPeriod = '19000105'

exec s_nrSyncDir
	@LocalDir1 = 'c:\BackupCopy\MyServer\Log\' ,
	@LocalDir2 = 'c:\BackupCopy\MyServer\Log\Archive\' ,
	@RemoteDir = '\\svr1\c$\Backup\Log\' ,
	@FileMask = '*.bak' ,
	@RetainPeriod = '19000105'

This will copy all files more recent than @RetainPeriod (5 days) from @RemoteDir to @LocalDir1 that are not found in @LocalDir1 or @LocalDir2.

After running this copy the latest full backup of the database from c:\BackupCopy\MyServer\Full\ to c:\BackupCopy\MyServer\Log\

Schedule this job to run - or add it as a second task to the job above.
I would suggest creating a separate job for copying the full backups and add this to the log file copy.

exec s_RestoreDatabase
	@SourcePath = 'c:\BackupCopy\MyServer\Log\' ,
	@archivePath = 'c:\BackupCopy\MyServer\Log\Archive\' ,
	@DataPath = 'c:\sql\data\' ,
	@LogPath = 'c:\sql\log\' ,
	@recover = 'norecovery' ,
	@recipients = '' ,
	@Database = 'mydb'

This will now drop the database mydb if it exists, restore the full backup moving the data and log files to the requested directories and restore all the logs.
The database will be left in standby mode waiting for more logs to arrive.
Any logs timestamped before the full backup are moved to c:\BackupCopy\MyServer\Log\Archive\ before the full database restore.
After each file is restored it is moved to c:\BackupCopy\MyServer\Log\Archive\. Hence the requirement for two local folders in s_nrSyncDir.

If the log shipping breks down - corrupt log file or someone has interferred with the source database then it must be restarted.
Stop the restore job.
Copy the latest full backup into c:\BackupCopy\MyServer\Log\.
Recover the database (restore datbase mydb with recovery) - this should really be included in the restore procedure.
Restart the restore job.



home