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