Skip to content

No Brainer SQL SERVER Log Shipping With Quest’s LiteSpeed

April 30, 2009

I am doing some dabblings on Quest Software’s LiteSpeed. It’s cool that LiteSpeed can give you familiar options in its GUI. If you’re new to Log Shipping, instead of reading pages after pages of tutorials, take a look at the script that LiteSpeed created and see how it is being done.

Note: I only tested this within a single server. I did the log shipping between “Subcriber” and “Publisher” databases. Do you want to guess which is which?

Here’s the sample script that LightSpeed’s Log Shipping Plan Wizard created for me:

/* this script executes on publisher */
declare @plan_id as uniqueidentifier
declare @backup_job_id as uniqueidentifier
exec LogShipping_Add_PrimaryDatabase
    @Database = N'Publisher',
    @PlanID = @plan_id output,
    @BackupShare = N'\Devcomp',
    @BackupJobID = @backup_job_id output,
    @BackupPeriod = 15,
    @BackupRetentionPeriod = 1440,
    @BackupAcceptableLatency = 45,
    @BackupDirectory = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup',
    @PerformBackup = 1
     select @plan_id

/* this script executes on publisher */
exec LogShipping_Add_PrimarySubscriber
    @PrimaryDatabase = N'Publisher',
    @SecondaryServer = N'DEVCOMPSQLSERVER2005DEV',
    @SecondaryDatabase = N'Subscriber'

/* this script executes on subscriber DEVCOMPSQLSERVER2005DEV */
declare @restore_job as uniqueidentifier
declare @secondary as uniqueidentifier
exec LogShipping_Init_Subscriber
    @PlanID = @plan_id,
    @PrimaryServer = N'DEVCOMPSQLSERVER2005DEV',
    @PrimaryDatabase = N'Publisher',
    @BackupSourceDirectory = N'\Devcomp',
    @BackupDestinationDirectory = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup',
    @SecondaryDatabase = N'Subscriber',
    @RestoreJobID = @restore_job output,
    @SecondaryID = @secondary output,
    @RestoreDelay = 0,
    @FileRetentionPeriod = 1440,
    @CopyPeriod = 15,
    @RestorePeriod = 15,
    @RestoreMode = 0,
    @DisconnectUsers = 0,
    @RestoreAcceptableLatency = 30,
    @RestoreAffinity = 0,
    @RestoreDataDestinationDirectory = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data',
    @RestoreLogDestinationDirectory = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data',
    @PerformRestore = 1

kick it on

One Comment leave one →
  1. June 24, 2011 5:36 PM

    Marlon, I am using this proc LogShipping_Init_Subscriber do you know what @RestoreMode = 0 does? I am trying to set the tran restore to leave the db in readonly/standby

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

  • Archives

  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: