Log shipping med script

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

Förenkla  Log shipping uppsättningen med script

Att konfigurera Log shipping är en ganska enkel procedur när man har gjort det några gånger, vill man så kan man göra det med script så att man snabbt kan få upp Log shipping igen när man behöver. Grunderna till scriptet nedan kommer från generera script via SQL Management studio med några tillägg. Scriptet tar först Full backup av den primära databasen och gör restore till den sekundära servern. Sedan synkroniseras databasen och de tillhörande schemalagda jobben LS_Backup på den primära servern och LS_Copy och LS_Restore på den sekundära servern skapas.

 

Förbered scriptet

  • För att sätta upp Log shipping måste man vara sysadmin på SQL Servern
  • Databasen måste vara i full eller bulk-logged recovery mode
  • Skapa en folder för backup på den primära servern eller nätverkshare och tilldela SQL Server Service kontot read/write behörighet
    Dela ut mappen
  • Skapa en folder för den sekundära servern, tilldela SQL Server agent kontot på den sekundära servern read/write behörighet
    Dela ut mappen
  • Editera variablerna i scriptet med relevant info

Logship3

 

  • Exekvera i SQLCMD mode

SQLCMD

 

Observera!

Det är viktigt att man gör noggranna tester i testmiljö innan man använder det i produktionsmiljö.

 


/* LOG SHIPPING SCRIPT */

:ON ERROR exit

:setvar PrimaryServer "<PrimärServer>\<Instans>"

:setvar PrimaryDatabase "<Databas>"

:setvar PrimaryBackupDirectory "C:\Temp\logship\backup\"

:setvar PrimaryBackupShare "\\<PrimärServer>\Temp\logship\backup\"

:setvar SecondaryServer "<SekundärServer>\<Instans>"

:setvar SecondaryDatabase "<Databas>"

:setvar SecondaryBackupDirectory "\\<SekundärServer>\temp\logship\restore\"

--Full backup och restore
:setvar FullBackup "\\<PrimärServer>Temp\logship\backup\<Databas>.bak"

:setvar SecondaryDataPath "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2\MSSQL\DATA\<Databas>.mdf"

:setvar SecondaryLogPath "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2\MSSQL\DATA\<Databas>_Log.ldf"


:CONNECT $(PrimaryServer)

USE [master]

BACKUP DATABASE $(PrimaryDatabase) TO  DISK = N'$(FullBackup)' WITH NOFORMAT, INIT,  NAME = N'$(PrimaryDatabase)-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

:CONNECT $(SecondaryServer)

USE [master]

RESTORE DATABASE $(SecondaryDatabase) FROM  DISK = N'$(FullBackup)' WITH  FILE = 1,  MOVE N'$(PrimaryDatabase)' TO N'$(SecondaryDataPath)',  MOVE N'$(PrimaryDatabase)_log' TO N'$(SecondaryLogPath)',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

GO

SET NOCOUNT ON

/* Primary Server */

:CONNECT $(PrimaryServer)

BEGIN

USE msdb;

/* Check to see if the backup folder exists */

RAISERROR('Checking to see if $(PrimaryBackupDirectory) exists...',0,1) WITH NOWAIT

DECLARE @CheckDir NVARCHAR(2000)

DECLARE @ErrorMsg NVARCHAR(200)

DECLARE @DirInfo TABLE

(

FileExists BIT ,

FileIsADirectory BIT ,

ParentDirectoryExists BIT

)
SET @CheckDir = '$(PrimaryBackupDirectory)'

INSERT  INTO @DirInfo
( FileExists ,

FileIsADirectory ,
ParentDirectoryExists

)

EXECUTE [master].dbo.xp_fileexist @CheckDir

IF NOT EXISTS ( SELECT  *

FROM    @DirInfo

WHERE   FileExists = 0

AND FileIsADirectory = 1

AND ParentDirectoryExists = 1 )

BEGIN

SET @ErrorMsg = 'The primary backup directory $(PrimaryBackupDirectory) does not exist...quitting'

RAISERROR(@ErrorMsg,16,1) WITH NOWAIT

RETURN

END
RAISERROR('$(PrimaryBackupDirectory) exists, continuing...',0,1) WITH NOWAIT

RAISERROR('Enabling log shipping for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT

------

DECLARE @LS_BackupJobId  AS uniqueidentifier

DECLARE @LS_PrimaryId    AS uniqueidentifier

DECLARE @SP_Add_RetCode  As int

EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database

@database = N'$(PrimaryDatabase)'

,@backup_directory = N'$(PrimaryBackupDirectory)'

,@backup_share = N'$(PrimaryBackupShare)'

,@backup_job_name = N'LSBackup_$(PrimaryDatabase)'

,@backup_retention_period = 4320

,@backup_compression = 2

,@backup_threshold = 60

,@threshold_alert_enabled = 1

,@history_retention_period = 5760

,@backup_job_id = @LS_BackupJobId OUTPUT

,@primary_id = @LS_PrimaryId OUTPUT

,@overwrite = 1

IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)

BEGIN

DECLARE @LS_BackUpScheduleUID        As uniqueidentifier

DECLARE @LS_BackUpScheduleID         AS int

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N'LSBackupSchedule_$(PrimaryServer)'

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20160128

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_BackUpScheduleUID OUTPUT

,@schedule_id = @LS_BackUpScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_BackupJobId

,@schedule_id = @LS_BackUpScheduleID

EXEC msdb.dbo.sp_update_job

@job_id = @LS_BackupJobId

,@enabled = 1

END

EXEC master.dbo.sp_add_log_shipping_alert_job

EXEC master.dbo.sp_add_log_shipping_primary_secondary

@primary_database = N'$(PrimaryDatabase)'

,@secondary_server = N'$(SecondaryServer)'

,@secondary_database = N'$(SecondaryDatabase)'

,@overwrite = 1

--

RAISERROR('Log shipping enabled for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT

END

GO

/* Secondary Server */

:CONNECT $(SecondaryServer)

DECLARE @LS_Secondary__CopyJobId     AS uniqueidentifier

DECLARE @LS_Secondary__RestoreJobId  AS uniqueidentifier

DECLARE @LS_Secondary__SecondaryId   AS uniqueidentifier

DECLARE @LS_Add_RetCode  As int

EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary

@primary_server = N'$(PrimaryServer)'

,@primary_database = N'$(PrimaryDatabase)'

,@backup_source_directory = N'$(PrimaryBackupShare)'

,@backup_destination_directory = N'$(SecondaryBackupDirectory)'

,@copy_job_name = N'LSCopy_$(PrimaryServer)_$(PrimaryDatabase)'

,@restore_job_name = N'LSRestore_$(PrimaryServer)_$(PrimaryDatabase)'

,@file_retention_period = 4320

,@overwrite = 1

,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT

,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT

,@secondary_id = @LS_Secondary__SecondaryId OUTPUT

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)

BEGIN

DECLARE @LS_SecondaryCopyJobScheduleUID           As uniqueidentifier

DECLARE @LS_SecondaryCopyJobScheduleID            AS int

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N'DefaultCopyJobSchedule'

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20160128

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__CopyJobId

,@schedule_id = @LS_SecondaryCopyJobScheduleID

DECLARE @LS_SecondaryRestoreJobScheduleUID        As uniqueidentifier

DECLARE @LS_SecondaryRestoreJobScheduleID         AS int

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N'DefaultRestoreJobSchedule'

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20160128

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__RestoreJobId

,@schedule_id = @LS_SecondaryRestoreJobScheduleID

END

DECLARE @LS_Add_RetCode2 As int

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)

BEGIN

EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database

@secondary_database = N'$(SecondaryDatabase)'

,@Primary_server = N'$(PrimaryServer)'

,@Primary_database = N'$(PrimaryDatabase)'

,@restore_delay = 0

,@restore_mode = 0

,@disconnect_users       = 0

,@restore_threshold = 45

,@threshold_alert_enabled = 1

,@history_retention_period           = 5760

,@overwrite = 0

END

IF (@@error = 0 AND @LS_Add_RetCode = 0)

BEGIN

EXEC msdb.dbo.sp_update_job

@job_id = @LS_Secondary__CopyJobId

,@enabled = 1

EXEC msdb.dbo.sp_update_job

@job_id = @LS_Secondary__RestoreJobId

,@enabled = 1

END

RAISERROR('Setup of log shipping from database $(PrimaryDatabase) on server $(PrimaryServer) to database $(SecondaryDatabase) on $(SecondaryServer) complete!',0,1) WITH NOWAIT

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Läs liknande inlägg