SQL SERVER 数据库备份

/**
    批量循环备份用户数据库，做为数据库迁移临时用
*/

set nocount on

declare @d varchar(8) 
declare @backup_flag nvarchar(10)
  
set @d=convert(varchar(8),getdate(),112) 

/***自定义选择备份哪些数据库****/
--set @backup_flag='userdb'           -- 所用的用户数据库
set @backup_flag='alwaysondb'       -- alwayson 用户数据库

create table #t (id int not null identity(1,1),sqlbak nvarchar(max) not null)

if @backup_flag='userdb'
begin 

    insert into #t (sqlbak)
    select 
            'backup database [' + name + '] to disk=''e:\backup\' + name + '_full_'+@d+'.bak'' with checksum,noformat,init,skip,compression'  as 'sqlbak'
    from    sys.databases
    where   database_id>4

end



if @backup_flag='alwaysondb' 
begin 

    insert into #t (sqlbak)
    select  
            'backup database [' + database_name + '] to disk=''e:\backup\' + database_name + '_full_'+@d+'.bak'' with checksum,noformat,init,skip,compression'  as 'sqlbak'
    from    sys.availability_databases_cluster
end



declare 
    @minid int ,
    @maxid int ,
    @sql varchar(max)
select  @minid = min(id) ,
        @maxid = max(id)
from    #t

print n'--打印备份脚本..........'


while @minid <= @maxid
    begin
        select  @sql = sqlbak
        from    #t
        where   id = @minid
  ----exec (@sql)
        print ( @sql )
        set @minid = @minid + 1
    end

drop table #t