Skip to main content

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.


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


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

This is used internally by RestoreLog



  1. Hi! Nice Post!

    I'll give it a try, if i dare! This is exactly my problem! How can they say SQL Log Shipping is supported when it's only half way true? But should one expect from free software..

  2. Hello, thank you for your article. But I have a problem with storing log with procedure. Problem is, that I get error with alert "Can't open backup device." I setuped some privileges to destination folder with Everyone privileges, but without success.

    Thank you for help.

  3. Thanks for the log creation guide!

  4. Hi, I can´t create procedure in database II because it is in readonly mode. I cant find a way to set readonly to true.
    I´m getting this message : la base de datos esta en estado de espera activa

  5. This is such a great resource that you are providing and you give it away for free. I love seeing blogs that understand the value of providing a quality resource about shipping. Best Oracle Fusion Financials Online Training

  6. Great Article IoT Projects for Students

    Deep Learning Projects for Final Year

    JavaScript Training in Chennai

    JavaScript Training in Chennai

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

  7. Thanks for sharing the valuable information here. Keep sharing more informative articles.
    Business Process Outsourcing Services
    Mobile application development company

  8. Your blog had very good knowledge and that gave huge instructions and that was really commendable ideas. you have provided good knowledge on this topic please share more information with us.. Oracle Fusion Cloud Training

  9. Replies
    1. I read your article it is very interesting and every concept is very clear, thank you so much for sharing. AWS Certification Course in Chennai

  10. Hey there, That was a a great information that you have provided. I am sure it will really help a lot of people out there. Keep sharing!

    SQL Server Load Rest API


Post a Comment

Popular posts from this blog

Freeing Disk Space on C:\ Windows Server 2008

  I just spent the last little while trying to clear space on our servers in order to install .NET 4.5 . Decided to post so my future self can find the information when I next have to do this. I performed all the usual tasks: Deleting any files/folders from C:\windows\temp and C:\Users\%UserName%\AppData\Local\Temp Delete all EventViewer logs Save to another Disk if you want to keep them Remove any unused programs, e.g. Firefox Remove anything in C:\inetpub\logs Remove any file/folders C:\Windows\System32\LogFiles Remove any file/folders from C:\Users\%UserName%\Downloads Remove any file/folders able to be removed from C:\Users\%UserName%\Desktop Remove any file/folders able to be removed from C:\Users\%UserName%\My Documents Stop Windows Update service and remove all files/folders from C:\Windows\SoftwareDistribution Deleting an Event Logs Run COMPCLN.exe Move the Virtual Memory file to another disk However this wasn’t enough & I found the most space was

3 Reasons Why Progressive Web Apps (PWAs) Won’t Replace Native Apps

Many people believe Progressive Web Apps (PWAs) are the future of the mobile web, but in my opinion, PWAs are not a replacement for native mobile apps. Here are three reasons why: 1. Native mobile apps provide a smoother & faster experience  Mobile websites, progressive or otherwise are slower and not as smooth. 90% of the time spent is spent using apps vs the browser . The single most significant contributing factor to a smooth experience on mobile is the speed of the network and latency of the data downloaded and uploaded. When you visit websites on desktop or mobile, there is a lot of third-party code/data that gets downloaded to your device, which more often than not has zero impact on the user experience. This includes: CSS (Cascading Style Sheets) JavaScript Ad network code Facebook tracking code Google tracking code The median number of requests a mobile website makes is a shocking  69 . On the other hand, native apps only get the data that is requi

CPF Contribution Rates for new Singapore Permanent Residents (SPR’s)

Recently my wife and I applied and got approved for Singapore Permanent Residency. After completing the formalities the most significant immediate change is the contribution to CPF which is Singapore’s mandatory social security savings scheme requiring contributions from employers and employees. CPF contributions start from the date you obtain SPR status, which is the date of the entry permit.   Being a relentless budgeter I needed to know exactly how much I and my employer would have to contribute so that I could adjust my budget accordingly as the employee contributions get deducted from the monthly salary. After doing some research I discovered that there is a “graduated” approach to CPF contributions for new SPR’s where the contributions gradually increase in the first and second year and then upon reaching the third year are at the full amount. Note: There is an option for employers to contribute the full amount for year 1 and year 2 and the employee can use the graduated ra