|
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,name sysname);
if right(@path,1)<>‘‘
set @[email?protected]+‘‘
if @dbname is null or @dbname in (‘all‘,‘*‘)
begin
--将所有数据库名存到一张临时表上
set @sql=‘select name from sys.databases where name not in (‘[email?protected]_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 [email?protected];
set @sql=‘backup ‘[email?protected]_type+‘ ‘[email?protected]+‘ to disk=‘+‘‘‘‘[email?protected][email?protected]_type+‘_‘[email?protected]+‘.‘+convert(varchar(8),getdate(),112)+‘.‘+DATENAME(HH,GETDATE())+‘‘‘‘
--开始循环备份
exec (@sql);
if @@ERROR<>0
insert into @backup_err_list(name) values(@dbname);
set @[email?protected]
end
end
else
begin
set @sql=‘backup ‘[email?protected]_type+‘ ‘[email?protected]+‘ to disk=‘+‘‘‘‘[email?protected][email?protected]_type+‘_‘[email?protected]me+‘.‘+convert(varchar(8),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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
