目的
实现MS SQL数据库备份及恢复标准化操作，提升服务器数据库运维操作能力。
适用范围
MS SQL 2005以上版本
责任人员
网络管理员/网络工程师
说明
master数据库。记录 SQL Server 实例的所有系统级信息。
msdb数据库。用于 SQL Server 代理计划警报和作业。
model数据库。用作 SQL Server 实例上创建的所有数据库的模板。 对 model 数据库进行的修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用于以后创建的所有数据库。
Resource 数据库。一个只读数据库，包含 SQL Server 包括的系统对象。 系统对象在物理上保留在 Resource 数据库中，但在逻辑上显示在每个数据库的 sys 架构中。
tempdb 数据库。一个工作空间，用于保存临时对象或中间结果集。
数据库备份
操作指南应用于windows2003系统SQL2008数据库迁移到windows2012系统SQL2008环境的不知道sa之类的一些密码和很作业链接服务器的场景。其中源服务器是指windows 2003系统，目的服务器是指windows 2012系统
系统环境
源服务器
操作系统：windows 2003 X64；
IP地址：192.168.22.14；
硬件配置：4核8G内存；
数据库：SQL2008 X64 – 10.0.1600.22
目的服务器
操作系统：windows 2012R2 X64；
IP地址：192.168.111.14；
硬件配置：8核8G内存；
数据库：SQL2008 X64 – 10.0.1600.22
源服务器端口检查
登陆源服务器，打开数据库的配置管理器。查看"SQL Native Client 10.0配置"中的"客户端协议"和"SQL Server网络配置"中"MSSQLSERVER的协议"内容。注意检查已开启的协议内容和对应的开放端口。
源服务器数据库路径检查
Master数据路径：C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
Master日志路径：C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
Model数据路径C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf
Model日志路径C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\modellog.ldf
Msdb数据路径C:\ProgramFiles\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf
Msdb日志路径C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf
Tempdb数据路径C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
Tempdb日志路径C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog.ldf
源服务器数据库实例名及路径检查
打开操作系统的运行界面，在"打开"中输入"Services.msc"进入系统服务器的设置界面，如下图所示：
检查数据库服务实例名：【MSSQLSERVER】和它对应的可以执行文件路径：
"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" –sMSSQLSERVER
检查数据库版本
打开源服务器的数据库查询分析器，输入以下的指令查询数据库的具体版本。实际的生产环境迁移要确保数据库版本一致。
select @@version
Microsoft SQL Server 2008 (RTM) – 【10.0.1600.22】 (X64)
Jul 9 2008 14:17:44
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) (VM)
源服务器数据库备份
数据库KEY备份 BACKUP SERVICE MASTER KEY TO FILE = 'D:\dbbaktest2\master_key.bak' ENCRYPTION BY PASSWORD = 'y1u2I3.4.5*6*' master库备份 BACKUP DATABASE master TO DISK = N'D:\dbbaktest2\master.bak' WITH NOFORMAT, NOINIT, NAME = N'master' , SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 model库备份 BACKUP DATABASE model TO DISK = N'D:\dbbaktest2\model.bak' WITH NOFORMAT, NOINIT, NAME = N'model' , SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 msdb库备份 BACKUP DATABASE msdb TO DISK = N'D:\dbbaktest2\msdb.bak' WITH NOFORMAT, NOINIT, NAME = N'msdb' , SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
数据库恢复
数据库安装
在目的服务器按装数据库之前，一定先改好操作系统账号密码，确保目的服务器和源服务器的操作系统账户和密码一致。否则有各种问题，有很多地方要改。
确保新安装的数据库版本是Microsoft SQL Server 2008- 【10.0.1600.22】，同时在安装是确保和源服务器的数据库实例名称一致。
在安装的过程中"服务账户"选项的"启动类型"设置为"禁用"，确保安装后数据库服务处于禁用状态。
选择混合模式并设置密码。
设置【master】【msdb】【model】的目录为之前源服务器上查到的目录地址，确保二者目标一致。
【Temdb】数据日志目录
确保安装完成都是成功状态，完成安装后，重启目标服务器。
目标服务器重启后，参照源服务器的情况检查目的服务器端口是否为1433，如下图所示。
进入系统服务界面，手工启动数据库服务，打开数据库企业管理器，参照5.4的内容检查SQL版本是否一致，确认后再手工禁用数据库服务。
恢复数据库
必须按顺序逐个还原master数据库、key、model数据库、msdb数据库。
首先进入单用户模式：进入系统服务的设置，找到数据库服务的启动项目，在数据库的启动参数中输入"-m"内容，如下图所示。
还原master数据库
RESTORE DATABASE master FROM DISK='D:\Dbbaktest2\master.bak' WITH REPLACE
还原服务器Key，重新启动SQL服务
RESTORE SERVICE MASTER KEY FROM FILE = 'D:\Dbbaktest2\master_key.bak' DECRYPTION BY PASSWORD = ' y1u2I3.4.5*6*' FORCE
连续执行三次指令，验证输入的密码是否一致。输入正常的情况如下图所示：
在数据库恢复时如果忘记源服务器的系统管理员密码和数据库SA的密码（数据库采用集成验证方式），当去掉目的服务器数据库的单用户模式，使用默认的模式并重新启动SQL服务，在登录目的服务器的数据库时由于密码不对会出现以下的错误提示：
新增数据库SA账号
首先重新调为数据库启动方式为单用户模式，重启数据库服务器后打开操作系统的命令行，在命令提示符输入："sqlcmd"进入数据库管理操作，输入以下的命令修改数据库Sa的密码为'123456'。
EXECUTE sp_password NULL,'123456','sa' GO
去掉单用户模式，用sa登录，删除本地管理员用户，再次增加本地管理员用户。
还原model数据库
RESTORE DATABASE model FROM DISK='D:\Dbbaktest2\model.bak' WITH REPLACE
还原msdb数据库
RESTORE DATABASE msdb FROM DISK='D:\Dbbaktest2\msdb.bak' WITH REPLACE
测试连接服务器
还原业务数据库
当系统数据库恢复后，相关的用户和链接服务器也正常了，只需要把源服务器中业务数据库相对应目录的mdf和ldf文件复制到目的服务器相同目录即可。 
业务数据库文件复制到相同目录后，数据库自动恢复正常。
参考资源
生产数据库指标参考
以下的数据库指标取自2018年7月份13000家门店访问的数字营运平台（192.168.0.209）。
源服务器
操作系统：windows 2008 R2 X64；
IP地址：192.168.0.209；
硬件配置：24核64G内存RAID10配置；
数据库版本：SQL2008 X64 – 10.0.1600.22
数据库大小：480G数据文件，35G日志文件，完整备份文件104G（启用压缩），8小时差异备份文件25.9G。
目的服务器
操作系统：windows 2012R2 X64；
IP地址：192.168.111.20；
硬件配置：8核8G内存；
数据库：SQL2012 X64
参考结论
104G完整备份文件网络拷贝时间35分钟。千兆网络，实际网络文件复制速度为50M/秒
网络拷贝25.9G差异备份文件，用了6分钟完成。千兆网络，实际网络文件复制速度为73M/秒
490G数据库8个小时的差异数据压缩备份时间为43分钟，备份文件大小为25.9GB。
恢复完整备份490G数据库使用了2个小时恢复完成。
恢复490G数据库为with norecovery模式,1个小时30分，差异备份25.9GB恢复模式为with recovery,恢复时间为6分钟。
添加评论