USE master
go
IF object_id('sp_backupdb','P') IS NOT NULL
DROP PROC sp_backupdb
go
CREATE PROC sp_backupdb
@db_list VARCHAR(4000) = '' , @type VARCHAR(128) = 'full' ,@dir_path VARCHAR(512) = 'E:\DB_BACKUP' ,@del_days_ago SMALLINT = 7
AS
/*
作者:陈恩辉-弘恩
*/
IF ISNULL(@type,'') NOT IN ('full','diff')
BEGIN
PRINT '参数 @type 只能是full,diff两种类型!'
RETURN
END
SET NOCOUNT ON ;
DECLARE @db_dir VARCHAR(4000) ,@db NVARCHAR(128)
DECLARE c CURSOR FAST_FORWARD FOR
SELECT name FROM master.sys.databases
WHERE database_id > 4
AND CHARINDEX ( ',' + name + ',' ,','+@db_list+',' ) > 0
OPEN c
FETCH NEXT FROM c INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
--1.创建文件夹开始
SET @db_dir = @dir_path + '\' + @db
EXEC master.dbo.xp_create_subdir @db_dir
--1.创建文件夹结束
IF @type = 'full'
BEGIN
--2完整备份开始
DECLARE @sql_full_backup NVARCHAR(4000),@cmd_del_backup_files NVARCHAR(4000)
SET @sql_full_backup = ' BACKUP DATABASE ['+ @db +'] TO DISK = '''+ @dir_path + '\' + @db + '\' + @db + '_full_backup_'+ CONVERT(VARCHAR(10) ,GETDATE(),112) +'_'+ RIGHT('0'+ CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR),2) + RIGHT('0'+ CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR),2) + '.bak'' '
PRINT @sql_full_backup
EXEC (@sql_full_backup)
--2完整备份结束
--3删除历史文件,保留几天的备份文件
SET @cmd_del_backup_files = ' dir ' + @db_dir + ' /b/a '
--PRINT @cmd_del_backup_files
CREATE TABLE #backup_full_files ( files VARCHAR(512))
INSERT INTO #backup_full_files ( files )
EXEC xp_cmdshell @cmd_del_backup_files
IF NOT EXISTS (SELECT 1 FROM #backup_full_files WHERE files LIKE '%'+CONVERT(VARCHAR(10),GETDATE(),112)+'%'+'.bak')
BEGIN
SELECT 1
END
DECLARE c_del_files_full_backup CURSOR FAST_FORWARD FOR
SELECT files--,CAST(SUBSTRING(files,LEN(files)- 14,8) AS DATETIME) dt
FROM #backup_full_files
WHERE files LIKE '%.bak'
AND CASE WHEN ISNUMERIC(SUBSTRING(files,LEN(files)- 16,8)) = 1 THEN CAST(SUBSTRING(files,LEN(files)- 16,8) AS DATETIME) ELSE '1901-01-01' END < DATEADD(DAY,-1*ABS(@del_days_ago),GETDATE())
OPEN c_del_files_full_backup
FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd_del_backup_files = 'del ' + @db_dir + '\' + @cmd_del_backup_files
PRINT @cmd_del_backup_files
EXEC xp_cmdshell @cmd_del_backup_files --执行删除CMD命令
FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files
END
CLOSE c_del_files_full_backup --关闭游标
DEALLOCATE c_del_files_full_backup --销毁游标
DROP TABLE #backup_full_files
--3删除历史文件,保留几天的备份文件
END
IF @type = 'diff'
BEGIN
DECLARE @sql_diff_backup NVARCHAR(4000),@cmd_del_backup_files_diff NVARCHAR(4000)
SET @sql_diff_backup = ' BACKUP DATABASE ['+ @db +'] TO DISK = '''+ @dir_path + '\' + @db + '\' + @db + '_diff_backup_'+ CONVERT(VARCHAR(10) ,GETDATE(),112) +'_'+ RIGHT('0'+ CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR),2) + RIGHT('0'+ CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR),2) + '.diffbak'' WITH DIFFERENTIAL '
PRINT @sql_diff_backup
EXEC (@sql_diff_backup)
SET @cmd_del_backup_files_diff = ' dir ' + @db_dir + ' /b/a '
PRINT @cmd_del_backup_files_diff
CREATE TABLE #backup_diff_files ( files VARCHAR(512))
INSERT INTO #backup_diff_files ( files )
EXEC xp_cmdshell @cmd_del_backup_files_diff
DECLARE c_del_files_full_backup CURSOR FAST_FORWARD FOR
SELECT files--,CAST(SUBSTRING(files,LEN(files)- 14,8) AS DATETIME) dt
FROM #backup_diff_files
WHERE files LIKE '%.diffbak'
AND CASE WHEN ISNUMERIC(SUBSTRING(files,LEN(files)- 14,8)) = 1 THEN CAST(SUBSTRING(files,LEN(files)- 16,8) AS DATETIME) ELSE '1901-01-01' END < DATEADD(DAY,-1*ABS(@del_days_ago),GETDATE())
OPEN c_del_files_full_backup
FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files_diff
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd_del_backup_files_diff = 'del ' + @db_dir + '\' + @cmd_del_backup_files_diff
PRINT @cmd_del_backup_files_diff
EXEC xp_cmdshell @cmd_del_backup_files_diff --执行删除CMD命令
FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files_diff
END
CLOSE c_del_files_full_backup --关闭游标
DEALLOCATE c_del_files_full_backup --销毁游标
DROP TABLE #backup_diff_files
END
FETCH NEXT FROM c INTO @db
END
CLOSE c --关闭游标
DEALLOCATE c --销毁游标
go