欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SqlServer批量备份多个数据库且删除3天前的备份

程序员文章站 2022-03-05 18:55:49
废话不多说了,直接给大家贴代码了,具体代码如下所示: /******************************************* * 批量备份...

废话不多说了,直接给大家贴代码了,具体代码如下所示:

/******************************************* 
 * 批量备份数据库且删除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; 

总结

以上所述是小编给大家介绍的sqlserver批量备份多个数据库且删除3天前的备份,希望对大家有所帮助