SQLSERVER数据库的作业的脚本及存储过程

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
 

查看更多关于SQLSERVER数据库的作业的脚本及存储过程的详细内容...