mysql 备份作业_SQL Server 作业的备份(备份作业非备份数据库)

Bertha 。 2022-11-05 03:06 328阅读 0赞

我的方法是把作业导出成文件备份起来，因为当你服务器维护的多了的时候很多你的作业 就很成问题，很麻烦

作业备份，不是备份数据库，是备份作业。

我的方法是把作业导出成文件备份起来，因为当你服务器维护的多了的时候很多你的作业 就很成问题，很麻烦。

最好能够作业实现同步，这个也是第一步，保存成文件，之后个人设想使用powershell 来把作业同步起来

DECLARE @jobname VARCHAR(30),@category\_calss\_i INT ,@category\_calss VARCHAR(50),@category\_name VARCHAR(50)

,@category\_type VARCHAR(30),@category\_id int

,@category\_type\_i int

SELECT @jobname = 'powershell',@category\_calss = '',@category\_name='',@category\_type = ''

SELECT @category\_calss = CASE WHEN tshc.category\_class = 1 THEN 'JOB'

WHEN tshc.category\_class = 2 THEN 'ALERT'

else 'OPERATOR'

END

, @category\_type = CASE WHEN tshc.category\_type = 1 THEN 'LOCAL'

WHEN tshc.category\_type = 2 THEN 'MULTI-SERVER'

else 'NONE'

END

,@category\_name = tshc.name

,@category\_type\_i = category\_type

,@category\_calss\_i = tshc.category\_class

,@category\_id = tshc.category\_id

FROM

msdb.dbo.sysjobs\_view AS sv

INNER JOIN msdb.dbo.syscategories AS tshc ON sv.category\_id = tshc.category\_id

WHERE

(sv.name=@jobname AND tshc.category\_class = 1)

PRINT ' BEGIN TRANSACTION'

PRINT 'DECLARE @ReturnCode INT'

PRINT 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''' + @category\_name +'''AND category\_class=' +rtrim(@category\_calss\_i)+')'

PRINT 'BEGIN'

PRINT 'EXEC @ReturnCode = msdb.dbo.sp\_add\_category @class=N'''+ @category\_calss+''', @type=N'''+@category\_type+''', @name=N'''+@category\_name+''''

PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

PRINT 'end'

DECLARE @EventLogLevel INT,@EmailLevel INT,@NetSendLevel INT,@PageLevel INT

DECLARE @EmailLeveloprid NVARCHAR(256),@NetSendLeveloprid NVARCHAR(256),@PageLeveloprid NVARCHAR(256)

DECLARE @isenable INT , @description NVARCHAR(1024),@owner\_log\_name Nvarchar(512),@delete\_level INT

DECLARE @jobId UNIQUEIDENTIFIER,@start\_step\_id INT,@server NVARCHAR(512)

SELECT

@EventLogLevel=sv.notify\_level\_eventlog

,@EmailLevel=sv.notify\_level\_email

,@NetSendLevel=sv.notify\_level\_netsend

,@PageLevel=sv.notify\_level\_page

,@EmailLeveloprid = ISNULL((SELECT TOP 1 name FROM msdb..sysoperators WHERE id = sv.notify\_email\_operator\_id),'')

,@NetSendLeveloprid = ISNULL((SELECT TOP 1 name FROM msdb..sysoperators WHERE id = sv.notify\_netsend\_operator\_id),'')

,@PageLeveloprid = ISNULL((SELECT TOP 1 name FROM msdb..sysoperators WHERE id = sv.notify\_page\_operator\_id),'')

,@isenable = sv.enabled

,@description = sv.description

,@owner\_log\_name = ISNULL(suser\_sname(sv.owner\_sid), N'''')

,@delete\_level = sv.delete\_level

,@jobId = sv.job\_id

,@start\_step\_id = start\_step\_id

,@server = originating\_server

FROM msdb.dbo.sysjobs\_view AS sv

WHERE (sv.name=@jobname and sv.category\_id=0)

PRINT 'DECLARE @jobId BINARY(16)'

PRINT 'EXEC @ReturnCode = msdb.dbo.sp\_add\_job @job\_name=N'''+@jobname+''','

PRINT ' @enabled='+RTRIM(@isenable)+', '

PRINT ' @notify\_level\_eventlog='+RTRIM(@EventLogLevel)+', '

PRINT ' @notify\_level\_email='+RTRIM(@EmailLevel)+', '

PRINT ' @notify\_level\_netsend='+RTRIM(@NetSendLevel)+', '

PRINT ' @notify\_level\_page='+RTRIM(@PageLevel)+', '

PRINT ' @notify\_email\_operator\_name ='''+RTRIM(@EmailLeveloprid)+''', '

PRINT ' @notify\_netsend\_operator\_name='''+RTRIM(@NetSendLeveloprid)+''', '

PRINT ' @notify\_page\_operator\_name='''+RTRIM(@PageLeveloprid)+''', '

PRINT ' @delete\_level='+RTRIM(@delete\_level)+', '

PRINT ' @description=N'''+@description+''', '

PRINT ' @category\_name=N'''+@category\_name+''', '

PRINT ' @owner\_login\_name=N'''+@owner\_log\_name+''', '

PRINT ' @job\_id = @jobId OUTPUT'

PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

\--SELECT \* FROM msdb.dbo.syscategories

DECLARE @step\_id INT

declare @step\_name nvarchar(512) ,@cmdexec\_success\_code INT,@on\_success\_action INT,@on\_success\_step\_id INT

,@on\_fail\_action INT,@on\_fail\_step\_id INT,@retry\_attempts INT,@retry\_interval INT,@os\_run\_priority INT

,@subsystem NVARCHAR(512),@database\_name NVARCHAR(512),@flags INT,@command NVARCHAR(max)

DECLARE jbcur CURSOR FOR SELECT step\_id FROM msdb..sysjobsteps WHERE job\_id = @jobid ORDER BY step\_id ;

OPEN jbcur;

FETCH NEXT FROM jbcur INTO @step\_id

WHILE @@FETCH\_STATUS = 0

BEGIN

SELECT @step\_name = step\_name

,@cmdexec\_success\_code= cmdexec\_success\_code

,@on\_success\_action = on\_success\_action

,@on\_success\_step\_id = on\_success\_step\_id

,@on\_fail\_action = on\_fail\_action

,@on\_fail\_step\_id = on\_fail\_step\_id

,@retry\_attempts = retry\_attempts

,@retry\_interval = retry\_interval

,@os\_run\_priority = os\_run\_priority

,@subsystem = subsystem

,@database\_name = database\_name

,@command = command

,@flags = flags

FROM msdb..sysjobsteps a WHERE job\_id = @jobid and step\_id = @step\_id

PRINT ' EXEC @ReturnCode = msdb.dbo.sp\_add\_jobstep @job\_id=@jobId, '

PRINT ' @step\_name=N'''+@step\_name+''', '

PRINT ' @step\_id='+RTRIM(@step\_id)+', '

PRINT ' @cmdexec\_success\_code='+RTRIM(@cmdexec\_success\_code)+', '

PRINT ' @on\_success\_action='+RTRIM(@on\_success\_action)+', '

PRINT ' @on\_success\_step\_id='+RTRIM(@on\_success\_step\_id)+', '

PRINT ' @on\_fail\_action='+RTRIM(@on\_fail\_action)+', '

PRINT ' @on\_fail\_step\_id='+RTRIM(@on\_fail\_step\_id)+', '

PRINT ' @retry\_attempts='+RTRIM(@retry\_attempts)+', '

PRINT ' @retry\_interval='+RTRIM(@retry\_interval)+', '

PRINT ' @os\_run\_priority='+RTRIM(@os\_run\_priority)+', @subsystem=N'''+@subsystem+''', '

PRINT ' @database\_name=N'''+@database\_name+''','

PRINT ' @flags='+RTRIM(@flags)+' ,'

PRINT ' @command=N'''+REPLACE(@command,'''','''''')+''''

PRINT ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

FETCH NEXT FROM jbcur INTO @step\_id

END

CLOSE jbcur

DEALLOCATE jbcur

PRINT ' EXEC @ReturnCode = msdb.dbo.sp\_update\_job @job\_id = @jobId, @start\_step\_id = '+rtrim(@start\_step\_id)

PRINT ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '

DECLARE @enabled INT,@freq\_type INT,@freq\_interval INT,@freq\_subday\_type INT,@freq\_subday\_interval INT

,@freq\_relative\_interval INT,@freq\_recurrence\_factor INT,@active\_start\_date INT,@active\_end\_date INT

,@active\_start\_time INT,@active\_end\_time INT,@name VARCHAR(512)

SELECT

@name = a.name

,@enabled = enabled

,@freq\_interval = freq\_interval

,@freq\_type = freq\_type

,@freq\_subday\_type=freq\_subday\_type

,@freq\_subday\_interval=freq\_subday\_interval

,@freq\_relative\_interval=freq\_relative\_interval

,@freq\_recurrence\_factor=freq\_recurrence\_factor

,@active\_start\_date=active\_start\_date

,@active\_end\_date=active\_end\_date

,@active\_start\_time=active\_start\_time

,@active\_end\_time=active\_end\_time

FROM msdb..sysschedules a

INNER JOIN msdb.dbo.sysjobschedules b ON a.schedule\_id = b.schedule\_id

WHERE job\_id = @jobId

IF(@name IS not null)

begin

PRINT ' EXEC @ReturnCode = msdb.dbo.sp\_add\_jobschedule @job\_id=@jobId, @name=N'''+@name+''', '

PRINT ' @enabled='+RTRIM(@enabled)+', '

PRINT ' @freq\_type='+RTRIM(@freq\_type)+', '

PRINT ' @freq\_interval='+RTRIM(@freq\_interval)+', '

PRINT ' @freq\_subday\_type='+RTRIM(@freq\_subday\_type)+', '

PRINT ' @freq\_subday\_interval='+RTRIM(@freq\_subday\_interval)+', '

PRINT ' @freq\_relative\_interval='+RTRIM(@freq\_relative\_interval)+', '

PRINT ' @freq\_recurrence\_factor='+RTRIM(@freq\_recurrence\_factor)+', '

PRINT ' @active\_start\_date='+RTRIM(@active\_start\_date)+', '

PRINT ' @active\_end\_date='+RTRIM(@active\_end\_date)+', '

PRINT ' @active\_start\_time='+RTRIM(@active\_start\_time)+', '

PRINT ' @active\_end\_time='+RTRIM(@active\_end\_time)+', '

PRINT ' @schedule\_uid=N'''+RTRIM(NEWID())+''''

PRINT ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

END

PRINT ' EXEC @ReturnCode = msdb.dbo.sp\_add\_jobserver @job\_id = @jobId, @server\_name = N'''+@server+''''

mysql 备份作业_SQL Server 作业的备份(备份作业非备份数据库)

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 sql server——备份系统数据库

相关 Linux下备份MySql备份数据库

相关数据库SQL Server 2014 设置自动备份（维护计划和作业）

相关 mysql 备份作业_SQL Server 作业的备份(备份作业非备份数据库)

相关 sql server增量备份mysql_sqlserver增量备份指令

相关 sql server2008数据库备份作业

相关 Sqlserver2014定时备份（维护计划/作业）

相关 sql server：维护计划实现备份：每周数据库完整备份、每天差异备份、每小时日志备份

相关 SQL Server数据库自动备份及删除自动备份文件

相关数据库SQL Server 2014 设置自动备份（维护计划和作业）