自动化SQL Server数据库备份脚本与存储过程示例

动备份SQL SERVER数据库的作业的脚本及存储过程

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)