sql server批量备份数据库且删除3天前备份的脚本

1.备份多个数据库，并删除3天前备份

/******************************************** 批量备份数据库且删除3天前的备份*******************************************/
DECLARE @backupfile VARCHAR(1024) 
DECLARE @backdesc VARCHAR(1024) 
DECLARE @filename VARCHAR(1024) 
DECLARE @path VARCHAR(1024) 
DECLARE @dbname VARCHAR(1024) 
DECLARE @extension_name VARCHAR(16) 
--备份参数 
DECLARE tmp_Cur CURSOR 
FOR 
  SELECT NAME 
  FROM  [sys].[databases] 
  WHERE  NAME NOT IN ( 'master', 'model','msdb','tempdb' ) 
SET @path = N'D:\Backup\Autoback\'; 
SET @extension_name = N'bak'; 
--生成文件名 
SET @filename = CONVERT(VARCHAR(1024), GETDATE(), 120) 
SET @filename = REPLACE(@filename, ':', '') 
SET @filename = REPLACE(@filename, '-', '') 
SET @filename = REPLACE(@filename, ' ', '') 
SET @filename = @filename + '_' + CONVERT (VARCHAR(3), DATEPART(ms, GETDATE())) 
  + N'.' + @extension_name 
OPEN tmp_Cur; 
FETCH NEXT FROM tmp_Cur INTO @dbname; 
WHILE @@FETCH_STATUS = 0  
  BEGIN 
    -- 得到完整目标文件，数据库将备份到这个文件中 
    SET @backupfile = @path + @dbname + @filename 
    --SELECT @backupfile 
    SET @backdesc =@dbname + N'-完整 数据库 备份' 
    -- 开始备份, COMPRESSION 参数表示压缩，可节省磁盘空间 
    BACKUP DATABASE @dbname TO DISK = @backupfile WITH NOFORMAT, NOINIT, NAME = @backdesc, SKIP, NOREWIND, NOUNLOAD, STATS = 10, COMPRESSION 
    FETCH NEXT FROM tmp_Cur INTO @dbname 
  END 
CLOSE tmp_Cur; 
DEALLOCATE tmp_Cur; 
-- 删除3天前的备份文件 
DECLARE @olddate DATETIME 
SELECT @olddate = DATEADD(d, -3, GETDATE()) 
-- 执行删除 (SQL 2008 具备) 
EXECUTE master.dbo.xp_delete_file 0, @path, @extension_name, @olddate, 1
[sql] view plain copy print?
--作业定时压缩脚本支持多库
DECLARE @DatabaseName NVARCHAR(50)
DECLARE @ExecuteSql NVARCHAR(MAX)
SET @ExecuteSql=''
DECLARE name_cursor CURSOR
FOR
  SELECT name FROM master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 
  'northwind','pubs','AgentSys','ydttimedtask','YiDianTongV2' ) 
OPEN name_cursor; 
FETCH NEXT FROM name_cursor INTO @DatabaseName; 
WHILE @@FETCH_STATUS = 0
  BEGIN  
    SET @ExecuteSql =''
    SET @ExecuteSql +='
      USE ['+@DatabaseName+'];
      DECLARE @Error INT
      SET @Error=(SELECT TOP 1 size/128.0 - CAST(FILEPROPERTY([NAME], ''SpaceUsed'') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files ORDER BY [NAME] DESC)
      --PRINT @Error
      IF(@Error>1)
        BEGIN
          ALTER DATABASE ['+@DatabaseName+']　　--数据库名字
          SET RECOVERY SIMPLE;　　--设置简单恢复模式
          DBCC SHRINKFILE ([YiDianTongV2], 1);　　--(M)不能小于1M,
          DBCC SHRINKFILE ([YiDianTongV2_log], 1);　　--(M)不能小于1M
          ALTER DATABASE ['+@DatabaseName+']
          SET RECOVERY FULL;　　--恢复为原来完整模式
     END
    '
    PRINT @ExecuteSql; --打印
    EXEC(@ExecuteSql) --执行
    FETCH NEXT FROM name_cursor INTO @DatabaseName; 
  END; 
CLOSE name_cursor; 
DEALLOCATE name_cursor;

2.备份多个数据库（除系统数据库外）

CREATE proc [dbo].[usp_autoBackupDB]
@dbname sysname=null --要备份的数据库名，不指定即为全部备份
,@path nvarchar(128)='d:\' --备份目录路径
,@backup_type varchar(16)='database' --备份类型，可以为database,log
,@backup_sysdb int=0 --是否备份系统数据库，0为不备份，1为备份
as
set nocount on;

declare @dbcnt int =0
,@sql varchar(2000)=''
,@except_db varchar(1000)=case @backup_sysdb 
when 0 then ''''+'master'+''''+','+'''' +'msdb'+''''+','+''''+'tempdb'+''''+','+''''+'model'+''''
when 1 then '' end;
declare @db_list table(id int identity(1,1) not null,name sysname);
declare @backup_err_list table(id int identity(1,1) not null,name sysname);
if right(@path,1)<>'\'
set @path=@path+'\'

if @dbname is null or @dbname in ('all','*')
begin
--将所有数据库名存到一张临时表上
set @sql='select name from sys.databases where name not in ('+@except_db+');'
insert into @db_list(name) exec(@sql);

--得到一共有多少个数据库
select @dbcnt=count(1) from @db_list;
--开始循环
while @dbcnt>0
begin
--从临时表中获得最后一个数据库的名字
select @dbname=name from @db_list where id=@dbcnt;
set @sql='backup '+@backup_type+' '+@dbname+' to disk='+''''+@path+@backup_type+'_'+@dbname+'.'+convert(varchar(8),getdate(),112)+'.'+DATENAME(HH,GETDATE())+''''
--开始循环备份
exec (@sql);
if @@ERROR<>0
insert into @backup_err_list(name) values(@dbname);
set @dbcnt=@dbcnt-1
end
end
else
begin
set @sql='backup '+@backup_type+' '+@dbname+' to disk='+''''+@path+@backup_type+'_'+@dbname+'.'+convert(varchar(8),getdate(),112)+'.'+DATENAME(HH,GETDATE())+''''
--仅备份一次
exec (@sql);
if @@ERROR<>0
insert into @backup_err_list(name) values(@dbname);

end
if exists(select * from @backup_err_list)
select ID ,name as 'backup_err_dbname' from @backup_err_list;
else print 'backup success';

set nocount off;

GO

3、备份单个数据库

create proc [dbo].[zldc_autoBackupDB]
@dbname nvarchar(50),
@path nvarchar(250)='D:\ceshi\'
as
begin
declare @date nvarchar(10) --定义日期变量
set @date = CONVERT(nvarchar(10),getdate(),112) --为日期变量赋当前日期，日期格式为 yyyymmdd 举例 20170830
--declare @path nvarchar(250) -- 定义备份路径变量
declare @db_filename nvarchar(150) --定义文件名变量
set @db_filename = @path + @dbname +@date+'.bak' --拼字符串，形成完整的备份文件路径
backup database @dbname TO DISK=@db_filename --执行数据库备份操作，注意 DBNAME为你实际要备份的数据库名，记得改
end

以上是琼杰笔记给大家介绍的SqlServer批量备份多个数据库且删除3天前的备份的脚本，大家有问题可以留言！