gpt4 book ai didi

sql-server - 编写日志传送自动化脚本

转载 作者:行者123 更新时间:2023-12-02 22:32:12 25 4
gpt4 key购买 nike

是否可以编写所有日志传送配置的脚本?

例如:我需要编写一个作业来启动从服务器 A 到服务器 B 的两个数据库的日志传送。

该作业将在服务器 A 上运行。

顺便说一下,两者都是 SQL 2008 R2 Enterprise。

服务器 A 上有共享。\serverA\日志

我相信这就是所需要的一切。是否可以仅通过脚本来完成?我是否必须初始化数据库,或者是否有像 SSMS 这样的选项来自动初始化?

我必须手动创建作业吗?或者它的配置方式是否可以创建作业并管理它们?

可以编写脚本吗?有人做过这样的事吗?有人有一个脚本可以做到这一点吗?

提前致谢。

最佳答案

这是完全可能的,并且可以通过日志传送设置 GUI 编写脚本。它生成的脚本中需要克服的唯一障碍将需要在同一脚本中连接到主数据库和辅助数据库。这可以通过使用 SQLCMD 的 SQLCMD 模式命令(使用 CONNECT 命令)来完成,确保在连接之间放置 GO。作为奖励,您还可以执行操作系统命令(复制、删除等),以便您也可以编写备份和恢复脚本。请参阅下面的示例。

-- Execute the following statements at the Primary to configure Log Shipping 
-- for the database [primary\instance1].[mydb],
-- The script needs to be run at the Primary in the context of the [msdb] database.
-------------------------------------------------------------------------------------
-- Adding the Log Shipping configuration

-- ****** Begin: Script to be run at Primary: [primary\instance1] ******

:setvar PRIMARY primary\instance1
:setvar SECONDARY secondary\instance2
:setvar DATABASE mydb
:setvar PBKDIR "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\"
:setvar PBKSHARE "\\primary\d$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\"
:setvar SBKSHARE "\\secondary\d$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\"
:setvar SDATADIR "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"


:connect $(PRIMARY)

alter database [$(DATABASE)] set recovery full

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'$(DATABASE)'
,@backup_directory = N'$(PBKDIR)'
,@backup_share = N'$(PBKSHARE)'
,@backup_job_name = N'LSBackup_$(DATABASE)'
,@backup_retention_period = 1440
,@backup_threshold = 180
,@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_$(PRIMARY)'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 60
,@freq_recurrence_factor = 0
,@active_start_date = 20100101
,@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'$(DATABASE)'
,@secondary_server = N'$(SECONDARY)'
,@secondary_database = N'$(DATABASE)'
,@overwrite = 1

backup database [$(DATABASE)] to disk = '$(PBKDIR)$(DATABASE)_dr_init.bak' with init
go


print '$(PBKSHARE)$(DATABASE)_dr_init.bak'
print '$(SBKSHARE)'
print 'copy $(PBKSHARE)$(DATABASE)_dr_init.bak $(SBKSHARE) /y'

!!copy $(PBKSHARE)$(DATABASE)_dr_init.bak $(SBKSHARE) /y
go

!!del $(PBKSHARE)$(DATABASE)_dr_init.bak /Q
go

-- ****** End: Script to be run at Primary: [primary\instance1] ******


-- Execute the following statements at the Secondary to configure Log Shipping
-- for the database [secondary\instance2].[mydb],
-- the script needs to be run at the Secondary in the context of the [msdb] database.
-------------------------------------------------------------------------------------
-- Adding the Log Shipping configuration

-- ****** Begin: Script to be run at Secondary: [secondary\instance2] ******

:connect $(SECONDARY)

!!sqlcmd /E /S$(SECONDARY) -i DRRestoreDatabase.sql -v BKDIR="$(SBKSHARE)" -v DATADIR="$(SDATADIR)" -v LOGDIR="$(SLOGDIR)"

!!del $(SBKSHARE)$(DATABASE)_dr_init.bak /Q
go


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'$(PRIMARY)'
,@primary_database = N'$(DATABASE)'
,@backup_source_directory = N'$(PBKSHARE)'
,@backup_destination_directory = N'$(SBKSHARE)'
,@copy_job_name = N'LSCopy_$(PRIMARY)_$(DATABASE)'
,@restore_job_name = N'LSRestore_$(PRIMARY)_$(DATABASE)'
,@file_retention_period = 1440
,@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 = 60
,@freq_recurrence_factor = 0
,@active_start_date = 20090505
,@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 = 60
,@freq_recurrence_factor = 0
,@active_start_date = 20090505
,@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'$(DATABASE)'
,@primary_server = N'$(PRIMARY)'
,@primary_database = N'$(DATABASE)'
,@restore_delay = 0
,@restore_mode = 1
,@disconnect_users = 1
,@restore_threshold = 180
,@threshold_alert_enabled = 1
,@history_retention_period = 2880
,@overwrite = 1

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
go


-- ****** End: Script to be run at Secondary: [secondary\instance2] ******

关于sql-server - 编写日志传送自动化脚本,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4127668/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com