全备示例
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
---------------------------------------------'
