SQL Server 系统数据库恢复

标签：SQL SERVER/MSSQL SERVER/数据库/DBA/故障恢复/master

概述

SQL Server 维护一组系统级数据库（称为“系统数据库”），这些数据库对于服务器实例的运行至关重要。每次进行大量更新后，都必须备份多个系统数据库。必须备份的系统数据库包括 msdb、master 和 model。如果有任何数据库在服务器实例上使用了复制，则还必须备份 distribution 系统数据库。备份这些系统数据库，就可以在发生系统故障（例如硬盘丢失）时还原和恢复 SQL Server 系统。

概述
正文
总结

正文

系统数据库说明

----1.resource
/*
包含SQLServer运行所需的关键系统表、元数、系统存储过程，它只包含系统相关的信息不包含用户相关的信息，在安装补丁的过程中将更改该数据库
*/
SELECT SERVERPROPERTY('ResourceVersion'),--返回数据库的最新版本
SERVERPROPERTY('ResourceLastUpdateDateTime'),--返回数据库的最后升级时间
SERVERPROPERTY('ProductUpdateReference')--返回升级的补丁信息

----2.master
/*
包含数据库相关配置、登录信息、实例相关配置信息
*/


----3.tempdb
/*
存储用户创建的临时对象（临时表、表变量等）、数据库引擎所需的临时对象、行版本信息等，tempdb数据库性能非常重要如果条件可以的话可以将其配置到当地的存储磁盘下，
ssd最佳。每次重启时tempdb库会重建。
*/

----4.model
/*
所有在实例上面新建的数据库都会参考模板数据库的相关配置进行创建（还原的数据库除外），新建的数据库相当于是copy一份model数据库的副本，包括model数据库的
数据库属性和数据库中创建的对象都会复制到新建的数据库中。
*/

----5.msdb
/*
包含SQL Server代理(作业、操作员、报警、策略以及作业历史的全部信息)、日志传输、SSIS、备份还原信息等操作信息。
*/

单用户启动实例

方法1：在配置管理器中，右键实例在启动参数中添加-m（注意是小写的m）,重新启动服务，处理完之后记得把-m参数去除重新启动服务。

方法2：在cmd中，使用net start mssqlserver /m，首先先停止所有相关的服务.

保持此窗口的情况下，数据库实例是无法进行连接登入的，所有出来完之后需要再次重启服务，重启的时候把/m参数去除。

方法3：在cmd中先定位到数据库安装目录“Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn”，输入sqlservr.exe –c –m,然后打开msms直接点新建查询使用管理员用户进入。

补充：配置管理员专用链接

运行输入cmd

输入Sqlcmd -A -S 127.0.0.1 或者Sqlcmd -A -S IP 或者Sqlcmd -A -S 机器名

如果是命名实例那么要在后面加上实例名：Sqlcmd -A -S 机器名\实例名

通过新建查询连接（文件-新建-数据库引擎查询）

ADMIN:IP或者ADMIN:127.0.0.1或者ADMIN:机器名

常用查询语句

select * from sys.dm_exec_requests

SELECT * from sysprocesses

select * from sys.dm_os_memory_cache_counters

select * from sys.dm_exec_sessions

还原master数据库

还原master的数据库必须在单用户启动实例，然后使用管理员用户进行还原。这也是文章前面讲单用户启动实例的目的

使用方法1和方法3启动实例之后，不要用往常登入的方式登入SQL管理工具，而是之间点击新建查询或者点击文件菜单-新建-数据库引擎查询，然后输入管理员权限的用户进入，接下来就是还原数据库了，还原语句很简单例如：

RESTORE DATABASE [master] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\master.bak' 
GO
如果失败则加上WITH REPLACE
RESTORE DATABASE [master] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\master.bak' 
WITH REPLACE

方法2有一个要注意的地方就是，在单用户启动实例之后进入sqlcmd工具，执行use master之后每一步记得加上go,否则一直是光标闪动，还有方法2只能还原master数据库无法还原其他数据库。

还原完之后再以正常的方式重新启动服务就好。

注意：如果重新生成master数据库之后（这里的重新生成和还原备份是不一样的，如果是还原最新的备份是不用再重新还原msdb和model数据库），一定要重新还原msdb和model数据库。

还原msdb数据库

还原 model 或 msdb 数据库与对用户数据库执行完整的数据库还原相同。不能还原用户正在访问的数据库。如果 SQL Server 代理正在运行，它可以访问 msdb 数据库。因此，在还原 msdb 之前，请先停止 SQL Server 代理。

msdb数据库需要在单用户模式下进行还原，这里说的单用户跟前面的master的单用户启动实例不一样，这里的单用户只是获取msdb数据库的单独访问权限，所以可以用语句将数据库设置成单用户模式然后执行还原。

USE [master]
GO
ALTER DATABASE [msdb] SET  SINGLE_USER WITH NO_WAIT
GO
RESTORE DATABASE [msdb] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\msdb.bak'
ALTER DATABASE [msdb] SET MULTI_USER WITH NO_WAIT

还原model数据库

还原model数据库与还原用户数据库的方法一样，这里就不做介绍。

还原Resource数据库

这个系统数据库无法进行备份还原，只能对文件进行备份；2008r2版本的sql的数据文件和日志文件在“D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn”路径下。

备份还原复制数据库

复制支持将复制的数据库还原到从中创建备份的同一服务器和数据库。如果将复制数据库的备份还原到其他服务器或数据库，则无法保留复制设置。在这种情况下，您必须在还原备份后重新创建所有发布和订阅（如果正在使用日志传送，则可以将复制数据库还原到备用服务器）。

应定期备份复制数据库及其关联系统数据库。备份下列数据库：

发布服务器上的发布数据库
分发服务器上的分发数据库
各个订阅服务器上的订阅数据库
发布服务器、分发服务器和所有订阅服务器上的 master 和 msdb 系统数据库。当备份这些数据库中的一个数据库或相关的复制数据库时，应同时备份这些数据库。例如，应在备份发布数据库的同时备份发布服务器上的 master 和 msdb 数据库。如果还原发布数据库，请确保 master 和 msdb 数据库在复制配置和设置方面与发布数据库保持一致。