INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Microsoft SQL Server: Setup and Administration FAQ

Backups

How to write your own Log Shipping by mrdenny
Posted: 14 Mar 05 (Edited 12 Apr 06)

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

This code is for SQL Server 2000
This is Step 1, it's type should be T/SQL
/*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

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

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

This is Step 4, it's type should be T/SQL
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

This is the restore job for SQL 7
/*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

This is Step 1, it's type should be T/SQL
/*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

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

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

This is Step 4, it's type should be T/SQL
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.

Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close