Microsoft’s Log Shipping is pretty good. But it requires that you have SQL Server Enterprise Edition on both the machines. This makes the solution fairly expensive. Because of this I’ve written a replacement which can be used on any edition of SQL Server including SQL Server Express Edition (as long as you use a different scheduler).
I recommend keeping the drive letters the same on the two machines, but this isn’t required.
First backup the database to log ship and restore it to the backup server using the WITH STANDYBY option. You are now prepped to start shipping the transaction log.
Setup a job on the primary server which backs up the log every few minutes to a network share on the backup server. I use code alone these lines.
backup log Northwind to disk='\backupsqlBackupFoldernorthwind.log' with NOINIT, NOSKIP, NOFORMAT
go
Add a second step to the job which uses osql to start a job on the backup server. Something like this. (The on failure action should be to Quit with Success for this step.)
osql -S BackupSQL -E -Q "msdb.dbo.sp_start_job 'Restore Northwind Log'"
Setup a restore job on the backup server (making sure that the name matches the job name in the second step of the backup job. The restore job will have four steps in it.
Step 1 (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
Step 2 (OS Command):
del d:RestoreFolderNorthwind.2.log
REM /*This removed the last file we processed.*/
Step 3 (OS Command):
move d:RestoreFolderNorthwind.log d:RestoreFolderNorthwind.2.log
REM /*This moves the current file into place for processing.*/
Step 4 (T/SQL):
declare @i int
declare @j int
set @j = 1
set @i = 0
restore headeronly from disk='d:RestoreFolderNorthwind.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:RestoreFolderNorthwind.2.log'
WITH FILE = @j,
STANDBY = 'F:MSSQLBackupRMDBArchive.sby' /*This keeps the database in standby mode ready for more restores.*/
set @j = @j + 1
END
If your folders don’t exist between the two servers you’ll need to add the MOVE parameter to the restore commands.
If you do being using this please post a note to the comments if you can. I like to know who is using this log shipping code.
