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
Hi! Nice Post!
ReplyDeleteI'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..
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.
ReplyDeleteThank you for help.
Thanks first of all for the useful info.the idea in this article is quite different and innovative please update more.
ReplyDeleteiOS Training in Chennai
iOS Training in T Nagar
JAVA Training in Chennai
Python Training in Chennai
Big data training in chennai
Selenium Training in Chennai
Android Training in Chennai
Thanks for the log creation guide!
ReplyDeleteHi, I can´t create procedure in database II because it is in readonly mode. I cant find a way to set readonly to true.
ReplyDeleteI´m getting this message : la base de datos esta en estado de espera activa
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
ReplyDeleteGreat Article IoT Projects for Students
ReplyDeleteDeep 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
education and e learning services
ReplyDeleteecommerce seo services
b2b seo services
enterprise seo services
Thanks for sharing the valuable information here. Keep sharing more informative articles.
ReplyDeleteBusiness Process Outsourcing Services
Mobile application development company
really useful .thank you for this post
ReplyDeletePython Training in chennai | Python Classes in Chennai
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
ReplyDeleteAwesome post Python Training in Chennai
ReplyDeleteI read your article it is very interesting and every concept is very clear, thank you so much for sharing. AWS Certification Course in Chennai
Deleterastgele görüntülü konuşma - kredi hesaplama - instagram video indir - instagram takipçi satın al - instagram takipçi satın al - tiktok takipçi satın al - instagram takipçi satın al - instagram beğeni satın al - instagram takipçi satın al - instagram takipçi satın al - instagram takipçi satın al - instagram takipçi satın al - binance güvenilir mi - binance güvenilir mi - binance güvenilir mi - binance güvenilir mi - instagram beğeni satın al - instagram beğeni satın al - polen filtresi - google haritalara yer ekleme - btcturk güvenilir mi - binance hesap açma - kuşadası kiralık villa - tiktok izlenme satın al - instagram takipçi satın al - sms onay - paribu sahibi - binance sahibi - btcturk sahibi - paribu ne zaman kuruldu - binance ne zaman kuruldu - btcturk ne zaman kuruldu - youtube izlenme satın al - torrent oyun - google haritalara yer ekleme - altyapısız internet - bedava internet - no deposit bonus forex - erkek spor ayakkabı - webturkey.net - karfiltre.com - tiktok jeton hilesi - tiktok beğeni satın al - microsoft word indir - misli indir
ReplyDeleteaşk kitapları
ReplyDeleteyoutube abone satın al
cami avizesi
cami avizeleri
avize cami
no deposit bonus forex 2021
takipçi satın al
takipçi satın al
takipçi satın al
takipcialdim.com/tiktok-takipci-satin-al/
instagram beğeni satın al
instagram beğeni satın al
btcturk
tiktok izlenme satın al
sms onay
youtube izlenme satın al
no deposit bonus forex 2021
tiktok jeton hilesi
tiktok beğeni satın al
binance
takipçi satın al
uc satın al
sms onay
sms onay
tiktok takipçi satın al
tiktok beğeni satın al
twitter takipçi satın al
trend topic satın al
youtube abone satın al
instagram beğeni satın al
tiktok beğeni satın al
twitter takipçi satın al
trend topic satın al
youtube abone satın al
takipcialdim.com/instagram-begeni-satin-al/
perde modelleri
instagram takipçi satın al
instagram takipçi satın al
takipçi satın al
instagram takipçi satın al
betboo
marsbahis
sultanbet
takipçi satın al
ReplyDeleteinstagram takipçi satın al
https://www.takipcikenti.com
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!
ReplyDeleteSQL Server Load Rest API
www.escortsmate.com
ReplyDeleteescortsmate.com
https://www.escortsmate.com
tiktok jeton hilesi
ReplyDeletetiktok jeton hilesi
referans kimliği nedir
gate güvenilir mi
tiktok jeton hilesi
paribu
btcturk
bitcoin nasıl alınır
yurtdışı kargo