完整备份

/****** Object:  StoredProcedure [dbo].[DB_Backup_Full]    Script Date: 2024/10/11 10:48:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



------------------------------------------------
CREATE PROCEDURE [dbo].[DB_Backup_Full]
AS

/*******************************************************************************************
<Create By> : wenjie.wang
<Create Date> : 2021_03_16
********************************************************************************************/

DECLARE @DBName VARCHAR (200)
SELECT @DBName=DB_NAME() --FROM master.dbo.sysprocesses where status = 'runnable'
--select @DBName
--Perform Full BackUp
DECLARE @FullFileName VARCHAR(200)
DECLARE @FileFlag VARCHAR(20)
--Delete local old backup file
DECLARE @SQLStr VARCHAR(300)

DECLARE @RemoteFileName VARCHAR(200)
Set @FileFlag='Full_20'+convert(char(6),getdate()-7,12)
Set @RemoteFileName='Z:\backup\full\'+@DBName+@FileFlag+'.BAK'
Set @SQLStr='Del '+@RemoteFileName
Execute master..xp_cmdshell @SQLStr
WaitFor Delay '00:00:20'  ---Wait for I/0

Set @FileFlag='Full_20'+convert(char(6),getdate(),12)
Set @FullFileName='Z:\backup\full\'+@DBName+@FileFlag+'.BAK'
BackUp DataBase @DBName To Disk=@FullFileName with init,
COMPRESSION,  
  ENCRYPTION   
   (  
   ALGORITHM = AES_256,  
   SERVER CERTIFICATE = DBBackupEncryptCert4_45 
   ),  
  STATS = 10 
---------------------------------------------'

GO

其中ENCRYPTION选项用了加密证书，如果不需要的话可以不加。

差异备份

/****** Object:  StoredProcedure [dbo].[DB_Backup_Diff]    Script Date: 2024/10/11 10:49:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



------------------------------------------------
CREATE PROCEDURE [dbo].[DB_Backup_Diff]
AS

/*******************************************************************************************
<Create By> : wenjie.wang
<Create Date> : 2021_03_16
********************************************************************************************/

DECLARE @DBName VARCHAR (200)
SELECT @DBName=DB_NAME()-- FROM master.dbo.sysprocesses where status = 'runnable'
DECLARE @FullFileName VARCHAR(200)
DECLARE @FileFlag VARCHAR(20)

--Delete local old backup file
DECLARE @SQLStr VARCHAR(300)
DECLARE @RemoteFileName VARCHAR(200)
SET @FileFlag='Diff_20'+CONVERT(CHAR(6),GETDATE()-3,12)
SET @RemoteFileName='Z:\backup\diff\'+@DBName+@FileFlag+'.BAK'
SET @SQLStr='Del '+@RemoteFileName
Execute master..xp_cmdshell @SQLStr
---------------------------------------------'

WaitFor Delay '00:00:20'  ---Wait for I/0


SET @FileFlag='Diff_20'+CONVERT(CHAR(6),GETDATE(),12)
SET @FullFileName='Z:\backup\diff\'+@DBName+@FileFlag+'.BAK'
BackUp DataBase @DBName To Disk=@FullFileName with init,differential

GO

事务日志备份

/****** Object:  StoredProcedure [dbo].[DB_Backup_Log]    Script Date: 2024/10/11 10:49:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




------------------------------------------------
CREATE PROCEDURE [dbo].[DB_Backup_Log]
AS

/*******************************************************************************************
<Create By> : wenjie.wang
<Create Date> : 2021_03_16
********************************************************************************************/
DECLARE @DBName VARCHAR (50)
SELECT @DBName=DB_NAME() 
DECLARE @FullFileName VARCHAR(200)
DECLARE @FileFlag VARCHAR(20)
DECLARE @Hour VARCHAR(2)
SET @FileFlag='Log_'+CONVERT(CHAR(8),GETDATE(),112) 
SET @Hour = DATENAME(HH,GETDATE())
IF LEN(@Hour) = 1 
BEGIN
 SET @Hour = '0'+@Hour
End
SET @FileFlag = @FileFlag +@Hour
SET @FullFileName='Z:\backup\log\'+@DBName+@FileFlag+'.BAK'
BackUp Log @DBName To Disk=@FullFileName with noinit
WaitFor Delay '00:00:5'  ---Wait for I/0

DECLARE @sql nvarchar(500)
SET @sql = 'DBCC Shrinkfile([' + @DBName +'_log],100)'
--此处收缩时如果逻辑名不一致会报8985的报错，可用select * from sys.database_files查看并替换
--Execute master..xp_cmdshell @sql
exec (@sql)
WaitFor Delay '00:00:5'  ---Wait for I/0

--Delete local old backup file
DECLARE @SQLStr varchar(300)
DECLARE @RemoteFileName varchar(200)
SET @FileFlag='Log_'+CONVERT(char(8),DATEADD(HOUR,-72,GETDATE()),112) + +@Hour
SET @RemoteFileName='Z:\backup\log\'+@DBName+@FileFlag+'.BAK'
SET @SQLStr='Del '+@RemoteFileName
Execute master..xp_cmdshell @SQLStr
---------------------------------------------'

GO

事务日志备份包含日志收缩功能（简单恢复模式无需进行日志备份和收缩）；
因需要截断日志才能收缩，因此在业务繁忙期可能一次备份无法截断，在使用agent创建计划作业调用存储过程时，可连续执行两次以完成收缩；
执行两次不会将事务日志备份覆盖，而是追加。

开启xp_cmdshell

使用上述存储过程操作OS文件需要开启xp_cmdshell，步骤如下：

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go