SQLServer备份存储过程

全备示例

USE [EAM]
GO
/****** Object:  StoredProcedure [dbo].[DB_Backup_Full]    Script Date: 2024/4/7 11:25:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------
ALTER 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()-28,12)
Set @RemoteFileName='E:\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='E:\backup\full\'+@DBName+@FileFlag+'.BAK'
BackUp DataBase @DBName To Disk=@FullFileName with init



---------------------------------------------'

差备示例

USE [EAM]
GO
/****** Object:  StoredProcedure [dbo].[DB_Backup_Diff]    Script Date: 2024/4/7 11:25:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------
ALTER 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()-7,12)
SET @RemoteFileName='E:\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='E:\backup\diff\'+@DBName+@FileFlag+'.BAK'
BackUp DataBase @DBName To Disk=@FullFileName with init,differential

日志备份示例

USE [EAM]
GO
/****** Object:  StoredProcedure [dbo].[DB_Backup_Log]    Script Date: 2024/4/7 11:25:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



------------------------------------------------
ALTER 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='E:\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
--select @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,-48,GETDATE()),112) + +@Hour
SET @RemoteFileName='E:\backup\log\'+@DBName+@FileFlag+'.BAK'
SET @SQLStr='Del '+@RemoteFileName
Execute master..xp_cmdshell @SQLStr
---------------------------------------------'