用游标备份单个或所有db

最新推荐文章于 2025-09-11 20:53:34 发布

转载最新推荐文章于 2025-09-11 20:53:34 发布 · 63 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/sandra/p/6098898.html

文章标签：

#数据库

CREATE procedure [dbo].[usp_BackupAllDB]
@databasename varchar(50)=null,
@backupdir varchar(100) =null

as
if @backupdir is null
set @backupdir='G:\sqldata\mssql\backup'
set nocount on
declare @dbname varchar(50),
@stime varchar(20),
@fname varchar(100),
@cmd varchar(200),
@sql varchar(1000)

if @databasename is not null
declare mycursor cursor for
select name
from master..sysdatabases
where name=@databasename
else
declare mycursor cursor for
select name
from master.sys.databases
where database_id not in (2,3)
and state_desc ='ONLINE'
order by name

open mycursor
fetch next from mycursor into @dbname
while @@fetch_status =0
begin
select @cmd = "exec master..xp_cmdshell 'if not exist "+@backupdir+"\"+@dbname+ " md "+@backupdir+"\"+@dbname+"'"
--select @cmd
exec (@CMD)

select @stime=replace(replace(replace(convert(varchar(19),getdate(),120),':',''),' ',''),'-','')
select @sql=" backup database "+@dbname +" to disk='"+@backupdir+"\"+@dbname+"\"+@dbname+"_"+@stime+"_D.BAK' WITH COMPRESSION"
exec (@sql)
select @sql
fetch next from mycursor into @dbname
end
close mycursor
deallocate mycursor

set nocount off

转载于:https://www.cnblogs.com/sandra/p/6098898.html