日期: 2011 年 9 月 6 日

由于项目关系，一台服务器数据库做迁移工作，将近两百多个数据库一个个备份要浪费很多时间，在此采用了生成脚本的方式与大家分享，希望给需要的人有帮助。已经测试适合MSSQL2005-MSSQL2008数据库备份和还原脚本，未测试MSSQL2000数据库。
2011-09-27 添加分离数据库和附加数据脚本

--批量生成分离脚本
use master
select 'EXEC sp_detach_db '''+name+''', ''true'';'  from sysdatabases 

--单个数据库分离
EXEC sp_detach_db '<database name>', 'true';

--批量生成数据库附加脚本
use master
select 'CREATE DATABASE ['+name+'] ON ( FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\'+name+'.mdf'' ),( FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\'+name+'_log.ldf'' ) FOR ATTACH ;' from sysdatabases 

--单个数据库附加脚本
use master
CREATE DATABASE [database_name] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\<database name>.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\<database name>.ldf' )
 FOR ATTACH ;

use master
--生成批量删除数据库脚本
select 'DROP DATABASE  ['+name+'];' from sysdatabases 

use master
--生成备份脚本，可在列表中复制生成的TSQL代码
select 'BACKUP DATABASE ['+name+'] TO DISK = ''f:\autobak\'+name+'.bak'' WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT ;' from sysdatabases 

如图所示：

复制以上列表TSQL脚本在迁移的服务器上进行执行备份数据库

--生成还原脚本，数据库存放目录与迁移机器同样时用此脚本
select 'BACKUP DATABASE ['+name+'] FROM DISK=''f:\autobak\'+name+'.bak'' WITH REPLACE ;' from sysdatabases

select 'alter database ['+name+']  set online ;' from sysdatabases 

如图所示：

--数据库迁移到其他机器，盘符和目录发生变化时，就需要做还原配置相应的路径
--D:\Data 目录为数据库存放目录和日志存放目录，也可将两个目录分其他盘符可提升IO的操作效率
--注意：MOVE 后面的数据逻辑名称，如果是独立创建的数据逻辑名不会错。
--     假如是从A数据库还原成B名称数据时，他的逻辑名称还是A，所以此处大家一定要注意
select 'RESTORE DATABASE ['+name+'] FROM DISK=''d:\autobak\'+name+'.bak'' 
WITH MOVE ''' +name+ ''' TO ''d:\Data\'+name+'.mdf'',
MOVE '''+name+'_log'' TO ''d:\Data\'+name+'_log.ldf'';' from sysdatabases

生成以上脚本，可把数据库复制到部署机器上后，修改好bak文件所在目录执行TSQL脚本即可
有什么问题欢迎大家共同探讨，有不足之处或是有更好的方法希望大家能与我分享。