SQL SERVER數據庫的作業的腳本及存儲過程

2024-08-31 01:03:17

字體：大中小

來源：轉載

供稿：網友

本站文章旨在為該問題提供解決思路及關鍵性代碼，并不能完成應該由網友自己完成的所有工作，請網友在仔細看文章并理解思路的基礎上舉一反三、靈活運用

IF EXISTS (SELECT name

FROM sysobjects

WHERE name = N'cg_DoBackupJob'

AND type = 'P')

DROP PROCEDURE cg_DoBackupJob

GO

CREATE PROCEDURE [cg_DoBackupJob]

@DataBaseName varchar(100),

@FileHead varchar(50),

@isFullBackup bit, -- 0 差量備份 1 完整備份

@FolderPath varchar(50) = 'f:/db_backup/',

@BackName varchar(100) = 'unknown', -- 描述字串

@isAppendMedia bit = 1 -- 0 覆蓋媒體 1 追加到媒體

AS

declare @filePath varchar(150)

declare @sql varchar(1000)

select @filePath=@FolderPath + @FileHead + '_' + case @isFullBackup when 1 then 'FullBackup' when 0 then 'DifferBackup' end + '_' + convert ( nvarchar(11) ,getdate() , 112 )

+ case @isFullBackup when 1 then '' when 0 then replace(convert(nvarchar(15),getdate(),114),':','') end

--print(@filePath)

select @sql ='BACKUP DATABASE [' + @DataBaseName + '] TO DISK = '''

+ @filePath + ''' WITH '

+ case @isAppendMedia when 0 then 'INIT' when 1 then 'NOINIT' end

+ ' , NOUNLOAD , '

+ case @isFullBackup when 0 then 'DIFFERENTIAL , ' when 1 then '' end

+ ' NAME = N''' + @BackName + '備份'', NOSKIP , STATS = 10, NOFORMAT'

execute(@sql)

--print(@sql)

GO

-- =============================================

-- example to execute the store procedure

-- =============================================

EXECUTE cg_DoBackupJob 'cg_access911','access911',1

GO

用系統存儲過程去創建作業，代碼如下：

BEGIN TRANSACTION

DECLARE @JobID BINARY(16)

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- 刪除同名的警報（如果有的話）。

SELECT @JobID = job_id

FROM msdb.dbo.sysjobs

WHERE (name = N'access911_每2周備份一次')

IF (@JobID IS NOT NULL)

BEGIN

-- 檢查此作業是否為多重服務器作業

IF (EXISTS (SELECT *

FROM msdb.dbo.sysjobservers

WHERE (job_id = @JobID) AND (server_id <> 0)))

BEGIN

-- 已經存在，因而終止腳本

RAISERROR (N'無法導入作業“access911_每2周備份一次”，因為已經有相同名稱的多重服務器作業。', 16, 1)

GOTO QuitWithRollback

END

ELSE

-- 刪除［本地］作業

EXECUTE msdb.dbo.sp_delete_job @job_name = N'access911_每2周備份一次'

SELECT @JobID = NULL

END

BEGIN

-- 添加作業

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'access911_每2周備份一次', @owner_login_name = N'Access911/access911', @description = N'沒有可用的描述。', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0

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

-- 添加作業步驟

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'2周備份', @command = N'EXECUTE cg_DoBackupJob ''a9SupperDatabase'',''a9SupperDatabase'',1

', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

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

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

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

-- 添加作業調度

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'diaodu', @enabled = 1, @freq_type = 8, @active_start_date = 20061009, @active_start_time = 0, @freq_interval = 64, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 2, @active_end_date = 99991231, @active_end_time = 235959

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

-- 添加目標服務器

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

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

END

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Transact-SQL 參考

sp_add_jobschedule

創建作業調度。

語法

sp_add_jobschedule [ @job_id = ] job_id, | [ @job_name = ] 'job_name',

[ @name = ] 'name'

[ , [ @enabled = ] enabled ]

[ , [ @freq_type = ] freq_type ]

[ , [ @freq_interval = ] freq_interval ]

[ , [ @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 ]

參數

[ @jobid = ] job_id

將向其中添加調度的作業的作業標識號。job_id 的數據類型為 uniqueidentifier，默認設置為 NULL。

[ @job_name = ] 'job_name'

作業的名稱，調度即添加到該作業中。job_name 的數據類型為 sysname，默認設置為 NULL。

說明必須指定 job_id 或 job_name，但不能兩個都指定。

[ @name = ] 'name'

調度的名稱。name 的數據類型為 sysname，沒有默認設置。

[ @enabled = ] enabled

指明調度的當前狀態。enabled 的數據類型為 tinyint，默認設置為 1（啟用）。如果為 0，則不啟用調度。禁用該調度時，不運行作業。

[ @freq_type = ] freq_type

用于指明何時將執行作業的值。freq_type 的數據類型為 int，默認設置為 0，可以是下列值之一。

值描述

1 一次

4 每天

8 每周

16 每月

32 每月，與 freq interval 相關

64 當 SQLServerAgent 服務啟動時運行

128 計算機空閑時運行

[ @freq_interval = ] freq_interval

作業執行的天數。freq_interval 的數據類型為 int，默認設置為 0，依賴于 freq_type 的值。

freq_type 的值對 freq_interval 的影響

1（一次）未使用 freq_interval。

4（每天）每個 freq_interval 日。

8（每周） freq_interval 為下面的一個或多個值（與 OR 邏輯運算符結合使用）：

1 = 星期日

2 = 星期一

4 = 星期二

8 = 星期三

16 = 星期四

32 = 星期五

64 = 星期六

16（每月）每月的 freq_interval 日。

32（每月相對） freq_interval 為下列值之一：

1 = 星期日

2 = 星期一

3 = 星期二

4 = 星期三

5 = 星期四

6 = 星期五

7 = 星期六

8 = 日

9 = 工作日

10 = 周末

64（當 SQLServerAgent 服務啟動時）未使用 freq_interval。

128 未使用 freq_interval。

[ @freq_subday_type = ] freq_subday_type

指定 freq_subday_interval 的單位。freq_subday_type 為 int 類型，其默認值為 0，且可以取下列值之一。

值描述（單位）

0x1 在指定的時間

0x4 分鐘

0x8 小時

[ @freq_subday_interval = ] freq_subday_interval

作業每次執行之間要出現的 freq_subday_type 周期數。freq_subday_interval 的數據類型為 int，默認設置為 0。

[ @freq_relative_interval = ] freq_relative_interval

如果 freq_interval 是 32（每月相對），則為每月中已調度作業的 freq_interval 的發生情況。freq_relative_interval 的數據類型為 int，默認設置為 0，可以是下列值之一。

值描述（單位）

1 第一頁

2 秒

4 第三個

8 第四個

16 最后一頁

[ @freq_recurrence_factor = ] freq_recurrence_factor

作業的已調度執行之間的周數或月數。只有當 freq_type 是 8、16 或 32 時，才使用 freq_recurrence_factor。freq_recurrence_factor 的數據類型為 int，默認設置為 0。

[ @active_start_date = ] active_start_date

作業可開始執行的日期。active_start_date 的數據類型為 int，默認設置為 NULL，該值表示當天的日期。日期的格式為 YYYYMMDD。如果 active_start_date 不為 NULL，則日期必須大于或等于 19900101。

[ @active_end_date = ] active_end_date

作業可停止執行的日期。active_end_date 的數據類型為 int，默認設置為 99991231，該值表示 9999 年 12 月 31 日。格式為 YYYYMMDD。

[ @active_start_time = ] active_start_time

在 active_start_date 和 active_end_date 之間的任何一天開始執行作業的時間。active_start_time 的數據類型為 int，默認設置為 000000，該值表示 24 小時制的上午 12:00:00，并且必須使用格式 HHMMSS 進行輸入。

[ @active_end_time = ] active_end_time

在 active_start_date 和 active_end_date 之間的任何一天停止執行作業的時間。active_end_time 的數據類型為 int，默認設置為 235959，該值表示 24 小時制的下午 11:59:59，并且必須使用格式 HHMMSS 進行輸入。

返回代碼值

0（成功）或 1（失敗）

結果集

無

注釋

SQL Server 企業管理器提供易于使用的圖形方法來管理作業，建議使用該方法創建和管理作業基本結構。

權限

執行權限默認授予 public 角色。

示例

此示例假設已經創建用來備份數據庫的 NightlyBackup 作業。它將作業添加到名為 ScheduledBackup 的調度中，并且在每天上午 1:00 執行。

USE msdb

EXEC sp_add_jobschedule @job_name = 'NightlyBackup',

@name = 'ScheduledBackup',

@freq_type = 4, -- daily

@freq_interval = 1,

@active_start_time = 10000

請參見

修改和查看作業

sp_delete_jobschedule

sp_help_jobschedule

sp_update_jobschedule

系統存儲過程