Log Shipping in SQL Server Express 2008

When you entrust your Applications and Data to the Cloud Based Service Providers such as GoGrid and Amazon EC2 it becomes absolutely critical to employ a strict Disaster Recovery strategy.

As part of of our strategy it was imperative that we have a failover for our SQL Server. The only problem was that SQL Server Express 2008 doesn’t support Log Shipping.
Well technically it does but SQL Server Express does not have SQL Server Agent.

Now I’m a big fan of SQL Server, but when it comes to doing something a bit more complicated the Express editions limitations become a bit of a problem, that’s why it’s time to find creative solutions.

To set up Log Shipping the first step is to Backup your database and then Restore it on another instance making sure set the Recovery state as “RESTORE WITH STANDBY”. See below

To get this going you’ll need three stored procedures, one in the Master database and two in the slave database.

Now when you are trying to do this across Networks and Hosting Providers you will need a reliable way of transferring the Logs from the Master server to the Slave. For this we use Windows Live Sync, but that’s a whole other blog post.

Here are the stored procedures you need to get this going.
NB: These only work in SQL Server 2008 and our not backward compatible.

BackupLog

All you do here is specify the path for the log and the name of the database you want the transaction log for.

USE [master] 
GO 
CREATE PROCEDURE [dbo].[BackupLog] 
        @LogPath nvarchar(500), 
    @DatabaseName nvarchar(100) 
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements. 
    SET NOCOUNT ON; 
    BACKUP LOG @DatabaseName to disk=@LogPath with NOINIT, NOSKIP, NOFORMAT 
END 
GO

 

RestoreLog

All you do here is specify the path for the log and the name of the database you want to restore the transaction log for.

CREATE PROCEDURE [dbo].[RestoreLog] 
    @LogPath nvarchar(500), 
    @DatabaseName nvarchar(100) 
AS 
BEGIN 
    SET NOCOUNT ON; 
    DECLARE @StandbyFile nvarchar(1000); 
    SET @StandbyFile = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\' + @DatabaseName + '.undo'; 
    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 = @DatabaseName) 
    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 

    DECLARE @i int 
    DECLARE @j int 
    SET @j = 1 
    SET @i = 0 
    RESTORE HEADERONLY FROM DISK=@LogPath 
    SET @i = @@ROWCOUNT 
    WHILE @i+1 > (@j) 
    BEGIN 
    RESTORE LOG @DatabaseName FROM DISK=@LogPath 
    WITH FILE = @j, 
    STANDBY = @StandbyFile 
    SET @j = @j + 1 
    END 

    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), 
    Flags int, 
    BindingID uniqueidentifier, 
    RecoveryForkID uniqueidentifier, 
    Collation varchar(255), 
    FamilyGUID uniqueidentifier, 
    HasBulkLoggedData bit, 
    IsSnapshot bit, 
    IsReadOnly bit, 
    IsSingleUser bit, 
    HasBackupChecksums bit, 
    IsDamaged bit, 
    BeginsLogChain bit, 
    HasIncompleteMetaData bit, 
    IsForceOffline bit, 
    IsCopyOnly bit, 
    FirstRecoveryForkID uniqueidentifier, 
    ForkPointLSN numeric(24, 0), 
    RecoveryModel varchar(256), 
    DifferentialBaseLSN numeric(24, 0), 
    DifferentialBaseGUID uniqueidentifier, 
    BackupTypeDescription varchar(256), 
    BackupSetGUID uniqueidentifier, 
    CompressedBackupSize int 
    ) 
    INSERT INTO #HeaderTable 
    EXEC master.dbo.GetRestoreCount @LogPath 
    SET @i = (SELECT COUNT(*) FROM #HeaderTable) 
    DROP TABLE #HeaderTable 
    WHILE @i+1 > (@j) 
    BEGIN 
    RESTORE LOG @DatabaseName FROM DISK=@LogPath 
    WITH FILE = @j, 
    STANDBY = @StandbyFile 
    SET @j = @j + 1 
    END 
END 
GO 

GetRestoreCount
This is used internally by RestoreLog

USE [master] 
GO 
SET ANSI_NULLS ON 
GO 

SET QUOTED_IDENTIFIER ON 
GO 

CREATE PROCEDURE [dbo].[GetRestoreCount] 
@FileName AS varchar(1000) 
AS 
RESTORE HEADERONLY FROM DISK=@FileName 

GO