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天前的备份,希望对大家有所帮助
上一篇: 帅哥你这手太直接了把
下一篇: 脉脉app怎么设置感兴趣的行业方向?