Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to write your own Log Shipping

Backups

How to write your own Log Shipping

by mrdenny Posted Mar 14, 2005 (Edited Apr 12, 2006)

Many people have asked about Log Shipping.

The log shipping built into SQL Server 2000 is not the greatest. It's flacky, has crappy reporting, and is a pain to fail over.

For those of you running SQL Server 7 you have to make a change to this because of some internal SQL 7 issues. Go to the Bottom of the Restore Server Code and I'll have the SQL 7 version in there.

These log shipping scripts won't get you a "hot standby" like the Microsoft Log Shipping (yeah you can fail over to it manually, and I'll cover how to do that later on).

First you need two servers. Both will need enough hard drive space to handle the task. The drives should be identical, with identical folder structors setup. The drives and folders don't need to be identical, but it will make things MUCH easier.

Lets cover some terms that I'm going to use.
Primary server - This is the normal production server.
Backup server - This is the secondary database server.

Now, extras that we need to setup.
We'll need a network share setup on the backup server for the primary server to write it's files to. For this example we'll use d:\BackupFolder. The NT Account that the SQL Servers run under will need to have full control of this folder.

First, do a full backup of the database we want to setup the log shipping for.

Code:

backup database northwind to disk='\\backupsql\BackupFolder\northwind.bak'
go

Once the backup is complete restore the database to the backup server, but leave the database is standby mode.

Code:

restore database northwind from disk='d:\BackupFolder\northwind.bak' WITH STANDBY='d:\MSSQL\MSSQL\Backup\Northwind.sby'
go

Now create a new job on the primary SQL Server. There will be two steps to this job. The first step will backup the transaction log, and write it to the backup servers network share.

Code:

backup log Northwind to disk='\\backupsql\BackupFolder\northwind.log' with NOINIT, NOSKIP, NOFORMAT
go

The second step of the job is an Operating System Command, so from the type drop down menu of the Job Step screen select "Operating System Command". This step will run osql and start the restore.

Code:

osql -S BackupSQL -E -Q "msdb.dbo.sp_start_job 'Restore Northwind Log'"

For this job, go to the Advanced Tab and set the On Failure Action to "Quit the job reporting success." The reason that we do this, is that we expect this step to fail every once and a while. As long as the first step doesn't fail we are ok.

Now on the backup server create a job named "Restore Northwind Log". It needs to have the same name as the second step of the job on the primary SQL Server.

This job will only have 4 steps. This job is a little more complex, so I'll put some comments inline to explain what it's doing.

Code:

[color red]This code is for SQL Server 2000[/color]
[blue]This is Step 1, it's type should be T/SQL[/blue]
/*This first part of the code ensures that no one is using the database that we are about to restore.  If we don't do this then the restore will fail.*/
declare @spid as varchar(10)
declare @CMD as varchar(1000)
declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid = 
		(select dbid from sysdatabases where name = 'Northwind')
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
	set @CMD = 'kill ' + @spid
	exec (@CMD)
	fetch next from cur into @spid
END
close cur
deallocate cur
go

[blue]This is Step 2, it's type should be Operating System Command[/blue]
del d:\RestoreFolder\Northwind.2.log
REM /*This removed the last file we processed.*/

[blue]This is Step 3, it's type should be Operating System Command[/blue]
move d:\RestoreFolder\Northwind.log d:\RestoreFolder\Northwind.2.log
REM /*This moves the current file into place for processing.*/

[blue]This is Step 4, it's type should be T/SQL[/blue]
declare @i int
declare @j int
set @j = 1
set @i = 0
restore headeronly from disk='d:\RestoreFolder\Northwind.2.log' /*This tells us how many transaction log backups there are in the file that we need to restore.*/
set @i = @@ROWCOUNT
while @i+1 > (@j)  /*This loop runs us through the file restoring the logs in order.*/
BEGIN
	restore log Northwind from disk='d:\RestoreFolder\Northwind.2.log'
		WITH FILE = @j,
			STANDBY = 'F:\MSSQL\Backup\RMDBArchive.sby' /*This keeps the database in standby mode ready for more restores.*/
	set @j = @j + 1
END

Code:

[color red]This is the restore job for SQL 7[/color]
/*Before Creating the job, create this stored procedure in the master database.*/
create procedure sp_GetRestoreCount
	@FileName as varchar(1000)
as
restore headeronly from disk=@FileName
go

[blue]This is Step 1, it's type should be T/SQL[/blue]
/*From here down is the code for the restore job.*/
declare @spid as varchar(10)
declare @CMD as varchar(1000)
declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid = 
		(select dbid from sysdatabases where name = 'Northwind')
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
	set @CMD = 'kill ' + @spid
	exec (@CMD)
	fetch next from cur into @spid
END
close cur
deallocate cur
go

[blue]This is Step 2, it's type should be Operating System Command[/blue]
del d:\RestoreFolder\Northwind.2.log
REM /*This removed the last file we processed.*/

[blue]This is Step 3, it's type should be Operating System Command[/blue]
move d:\RestoreFolder\Northwind.log d:\RestoreFolder\Northwind.2.log
REM /*This moves the current file into place for processing.*/

[blue]This is Step 4, it's type should be T/SQL[/blue]
declare @i int
declare @j int
set @j = 1
set @i = 0
create table #HeaderTable
	(BackupName varchar(255), BackupDescription varchar(255), BackupType int, ExpirationDate datetime, Compressed int,
	Position int, DeviceType int, UserName VarChar(255), ServerName varchar(255), DatabaseName varchar(255),
	DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(24,0), FirstLsn numeric(24,0),
	LastLsn numeric(24,0), CheckpointLsn numeric(24,0), DatabaseBackupLsn numeric(24,0), BackupStartDate datetime,
	BackupFinishDate datetime, SortOrder int, CodePage int, UnicodeLocaleID int, UnicodeComparisonStyle int,
	CompatibilityLevel int, SoftwareVendorID int, SoftwareVersionMajor int, SoftwareVersionMinor int,
	SoftwareVersionBuild int, MachineName varchar(255))
insert into #HeaderTable
exec sp_GetRestoreCount 'd:\RestoreFolder\Northwind.2.log'
set @i = (select count(*) from #HeaderTable)
drop table #HeaderTable
select @i
while @i+1 > (@j)  /*This loop runs us through the file restoring the logs in order.*/
BEGIN
	restore log Northwind from disk='d:\RestoreFolder\Northwind.2.log'
		WITH FILE = @j,
			STANDBY = 'F:\MSSQL\Backup\RMDBArchive.sby' /*This keeps the database in standby mode ready for more restores.*/
	set @j = @j + 1
END

If the folders aren't the same on the two servers then put the MOVE parameter into the restore log command.

You will want to setup both jobs so that if they fail, someone is paged. Do not schedule the job on the backup server. Only schedule the job on the primary server. Schedule it to run how ever often you want to have the log shipped to the backup server. I have run this with no problems every 5 minutes on a system that generated 50-60 megs of transaction logs every 5 minutes.

Now, to do a full backup of the primary database you will need to disable the job on the primary server. Be sure to re-enable the job when the full backup is done. If you have an automated backup software make sure that it can disable the job and re-enable the job when the full backup is done.

Now to explain what's happening.
Every 5 minutes the primary SQL Serer backs up the logs for the Northwind database to the backup server's hard drive. It does this no matter what the backup server is doing. If the OS for the backup server is up, the log gets backed up. Then it starts the restore process on the backup server. The reason that I have it use osql to start the job, and not run it trough the current job, is that I want it to process as quickly as possible. That way the job can restart at the next 5 minute mark.

When the backup job is started, it first kicks anyone using the database out of the database. Otherwise the restore will fail.

Then it deletes the backups file that has already been processed. It then moves the backup file into place for processing. It does this so that if the restore takes more than 5 minutes to backup job will still be able to backup the transaction log. It then reads the file do see how many backups are located within the file. It then restores the logs to the backup server in the order they were backed up. Logs need to be restored in order. If they aren't they won't restore.

If the job takes more than 5 minutes it's no problem. You can't start a job again while it's already running (which is why we aren't concerned if step 2 of the job on the primary SQL Server fails).

At this point your Log shipping should be up and running.

If you need to fail over to the backup server for some reason, let the restore job finish. Then run this code.

Code:

declare @j int
set @i = 0
restore headeronly from disk='d:\RestoreFolder\Northwind.2.log' /*This tells us how many transaction log backups there are in the file that we need to restore.*/
set @i = @@ROWCOUNT
restore log Northwind from disk='d:\RestoreFolder\Northwind.2.log'
	WITH FILE = @i

This code will restore the last transaction log to the SQL Server and put the database into read-write mode.

Failing back is a manual process. You'll need to backup and now production database running on backup, and restore it to primary, then setup the log shipping from the beginning again.

Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.

Accept Learn more…

Back

Top