批量迁移财务数据库(sqlserver)

--导出数据库名称的列表及其路径到临时表
if object_id('table1') is not null drop table table1
select filename as filename1,replace(filename,'mdf','_log@') as filename2, name  into table1  from master..sysdatabases

update table1 set filename2=replace(filenam2,'@','.ldf')

--批量分离数据库
declare @sqlStr varchar(4000)
select @sqlStr=''
select @sqlStr=@sqlStr+'EXEC  sp_detach_db ['+[name]+']'+CHAR(10)
from master..sysdatabases where name like 'USER%'

print @sqlStr --可以测试一下字符串是否正确
Exec(@sqlStr)

-附加数据库
declare @sqlStr varchar(4000)
select @sqlStr=''
select @sqlStr=@sqlStr+'EXEC  sp_attach_db  @dbname= '''+name+''',@filename1  ='''+filename1+''',@filename2='''+filename2+''''+CHAR(10)
from master..table1  where name like 'USER%'

print @sqlStr --可以测试一下字符串是否正确
exec(@sqlStr)