由于项目关系，一台服务器数据库做迁移工作，将近两百多个数据库一个个备份要浪费很多时间，在此采用了生成脚本的方式与大家分享，希望给需要的人有帮助。已经测试适合MSSQL2005-MSSQL2008数据库备份和还原脚本，未测试MSSQL2000数据库。
2011-09-27 添加分离数据库和附加数据脚本
1 2 3 4 5 6 | --批量生成分离脚本use masterselect 'EXEC sp_detach_db '''+name+''', ''true'';' from sysdatabases --单个数据库分离EXEC sp_detach_db '<database name>', 'true'; |
1 2 3 | --批量生成数据库附加脚本use masterselect '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 |
1 2 3 4 5 6 | --单个数据库附加脚本use masterCREATE 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 ; |
1 2 3 | use master--生成批量删除数据库脚本select 'DROP DATABASE ['+name+'];' from sysdatabases |
1 2 3 | use master--生成备份脚本，可在列表中复制生成的TSQL代码select 'BACKUP DATABASE ['+name+'] TO DISK = ''f:\autobak\'+name+'.bak'' WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT ;' from sysdatabases |
如图所示：
复制以上列表TSQL脚本在迁移的服务器上进行执行备份数据库
1 2 3 4 | --生成还原脚本，数据库存放目录与迁移机器同样时用此脚本select 'BACKUP DATABASE ['+name+'] FROM DISK=''f:\autobak\'+name+'.bak'' WITH REPLACE ;' from sysdatabasesselect 'alter database ['+name+'] set online ;' from sysdatabases |
如图所示：
1 2 3 4 5 6 7 | --数据库迁移到其他机器，盘符和目录发生变化时，就需要做还原配置相应的路径--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脚本即可
有什么问题欢迎大家共同探讨，有不足之处或是有更好的方法希望大家能与我分享。