平淡人生

静待花开,花开常有香相伴;倾听流水,流水总有乐相陪;有缘清风携舟涉碧水,无缘细雨伴君独自行。

MSSQL2005-MSSQL2008数据库批量备份和还原脚本

由于项目关系,一台服务器数据库做迁移工作,将近两百多个数据库一个个备份要浪费很多时间,在此采用了生成脚本的方式与大家分享,希望给需要的人有帮助。已经测试适合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脚本即可
有什么问题欢迎大家共同探讨,有不足之处或是有更好的方法希望大家能与我分享。


Categorised as: Windows技术