脚本日志运输自动化

是否可以编写所有日志传送配置的脚本？ 例如：我需要编写一个作业，从服务器A到服务器B的两个数据库启动日志传送。 该作业将在服务器A上运行。 顺便说一句，两者都是SQL 2008 R2 Enterprise。 服务器A上有一个共享。 serverA的登录 我相信这就是所需要的一切。 是不是可以通过脚本来做到这一点？ 我是否必须初始化数据库，或者是否有像SSMS这样的选项来自动初始化？ 我是否必须手动创建作业？或者它是以创建作业和管理作业的方式配置的？ 它是可编写脚本的吗？有人做过这样的事吗？ 有没有人有脚本来做这个？ 提前致谢。     
2019-07-23 2 条评论
分享

没有找到相关结果

    已邀请:

    距相镭

    这是完全可能的，它可以通过日志传送设置GUI编写。在它生成的脚本中要克服的唯一障碍是需要在同一脚本中连接到主要和次要。这可以通过使用CONNECT命令使用SQLCMD的SQLCMD模式命令来完成，确保在连接之间放置GO。作为奖励，您还可以执行操作系统命令（复制，删除等），以便您可以编写备份和还原脚本。见下面的例子。 
    -- Execute the following statements at the Primary to configure Log Shipping 
-- for the database [primaryinstance1].[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: [primaryinstance1] ******

:setvar PRIMARY primaryinstance1
:setvar SECONDARY secondaryinstance2
:setvar DATABASE mydb
:setvar PBKDIR "D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBACKUP"
:setvar PBKSHARE "\primaryd$Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBACKUP"
:setvar SBKSHARE "\secondaryd$Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBACKUP"
:setvar SDATADIR "D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA"


: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: [primaryinstance1]  ******


-- Execute the following statements at the Secondary to configure Log Shipping 
-- for the database [secondaryinstance2].[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: [secondaryinstance2] ******

: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: [secondaryinstance2] ******
        
    2019-07-23 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.044467926025391