批量分离附加迁移数据库

set nocount on

USE master

IF exists(SELECT 1 from tempdb.sys.objects where object_id=object_id('tempdb..#temp'))

drop TABLE tempdb..#temp

 

SELECT db_name(database_id) as dbname,physical_name physical_name

INTO #temp

from sys.master_files

where db_name(database_id) in(

SELECT name FROM sys.databases

where database_id>4

AND name NOT IN(

　　　　select name from sys.databases where name not in ('master','tempdb','model','msdb','TravelMoneyDB') and state not in (2,1) 

　　　　--SELECT dbname from ConfigDB..AutoBackupRestoreSetting where IsRestore='T'

)

)

 

 

--detach

SELECT distinct 'EXEC master.dbo.sp_detach_db @dbname = N'''+dbname+''''

from #temp

 

--attach

while exists(SELECT 1 from #temp)

begin

    DECLARE @dbname varchar(100),@physical_name varchar(1000)

    DECLARE @sql varchar(max)

    DECLARE @sql1 varchar(max)=''

        begin

            SELECT  top 1 @dbname=dbname from #temp

            SELECT @sql='CREATE DATABASE '+@dbname+' ON '

            SELECT @sql1=@sql1+char(10)+'( FILENAME = N'''+physical_name+''' ),' from  #temp

            where dbname=@dbname

            select @sql=@sql+@sql1

            select @sql=left(@sql,len(@sql)-1)+char(10)+'FOR ATTACH'+char(10)+'GO'

            print '----------'

            print @sql

            delete FROM #temp where dbname=@dbname

        end    

end

 

 

 

--将数据库还原成原来状态 offline 或 readonly

select 'alter database '+cast (name as nvarchar(32))+' set '+case when (is_read_only = 1 and state=0 )then 'read_only' else state_desc end ,b.state
from
(
select distinct db_name(b.database_id) as name,a.state,a.is_read_only,a.state_desc from sys.databases a join sys.master_files b on a.name=db_name(b.database_id)
) b
where b.name not in ('master','tempdb','model','msdb','TravelMoneyDB') and state not in (2,1)
and b.name not in
(
select name from sys.databases where state=0 and is_read_only =0
)
order by state desc

 

 

 

 

--standy模式  ，可以读取，也可以继续restore log

restore database  CarOrderIDDB  with standby='e:\dail_log.bk' 

 

restore database CarOrderIDDB  with norecovery