One of the welcome
improvements in log shipping with SQL Server 2005 is the ability to script the
steps and execute them through code instead of having to use wizards, as was
required in SQL Server 2000. The following statements setup log shipping for
AdventureWorks sample database:
001./*
Execute the following statements at the SOURCE server to configure Log
Shipping 002.-- for
the database [AdventureWorks], 003.-- The
script needs to be run at the SOURCE server in the context of the [msdb]
database. Adding the Log Shipping configuration and
creating transaction log backup job on the source server */ 004.DECLARE @LS_BackupJobId AS UNIQUEIDENTIFIER005. 006.DECLARE @LS_PrimaryId AS UNIQUEIDENTIFIER007. 008.DECLARE @SP_Add_RetCode AS INT009. 010.EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database024. 025.IF (@@ERROR = 0026.AND @SP_Add_RetCode = 0)027.BEGIN028.DECLARE @LS_BackUpScheduleUID AS UNIQUEIDENTIFIER029. 030.DECLARE @LS_BackUpScheduleID AS INT031. 032.-- Add
schedule for the transaction log backup job:033.EXEC msdb.dbo.sp_add_schedule047. 048.EXEC msdb.dbo.sp_attach_schedule051. 052.--
enable the transaction log backup job:053.EXEC msdb.dbo.sp_update_job056.END057. 058.-- add a
record to MSDB database table for the target database:059.EXEC master.dbo.sp_add_log_shipping_primary_secondary064. 065./*
Execute the following statements at the TARGET server to configure Log Shipping066.-- for
the database [AdventureWorks],067.-- the
script needs to be run at the TARGET server in the context of the [msdb]
database. Adding the Log Shipping configuration */068.DECLARE @LS_Secondary__CopyJobId AS UNIQUEIDENTIFIER069. 070.DECLARE @LS_Secondary__RestoreJobId AS UNIQUEIDENTIFIER071. 072.DECLARE @LS_Secondary__SecondaryId AS UNIQUEIDENTIFIER073. 074.DECLARE @LS_Add_RetCode AS INT075. 076.--
configure log shipping and create copy / restore jobs on the target:077./* note:
if you use SQL Server authentication @monitor_security_mode parameter will be
set to 1. Login name and password will NOT be scripted - you will
have to specify them at script execution time */078.EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary092. 093.IF (@@ERROR = 0094.AND @LS_Add_RetCode = 0)095.BEGIN096.DECLARE @LS_SecondaryCopyJobScheduleUID AS UNIQUEIDENTIFIER097. 098.DECLARE @LS_SecondaryCopyJobScheduleID AS INT099. 100.--
create and attach schedule for copy job:101.EXEC msdb.dbo.sp_add_schedule115. 116.EXEC msdb.dbo.sp_attach_schedule119. 120.DECLARE @LS_SecondaryRestoreJobScheduleUID AS UNIQUEIDENTIFIER121. 122.DECLARE @LS_SecondaryRestoreJobScheduleID AS INT123. 124.--
create and attach schedule for restore job:125.EXEC msdb.dbo.sp_add_schedule139. 140.EXEC msdb.dbo.sp_attach_schedule143.END144. 145.DECLARE @LS_Add_RetCode2 AS INT146. 147.IF (@@ERROR = 0148.AND @LS_Add_RetCode = 0)149.BEGIN150.EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database161.END162. 163.IF (@@ERROR = 0164.AND @LS_Add_RetCode = 0)165.BEGIN166.EXEC msdb.dbo.sp_update_job169. 170.EXEC msdb.dbo.sp_update_job173.END
You can script log shipping configuration during the initial setup
or at any time after setup is complete. You can also alter log shipping
configuration through database properties' dialog even if it were setup through
Transact-SQL commands.
No comments:
Post a Comment