sql server 2005 & 2008 master与msdb数据库的备份与恢复，master与msdb数据库的备份与普通数据库一样，本文主要描述master与msdb数据库的恢复的一些个人积累。
恢复master与msdb数据库关键在于以单用户模式启动，其它并没有太大的区别。
由于系统数据库对sql server来说尤其重要，为了确保SQL SERVER系统的正常运行，除了日常备份用户的数据库之外，我们还需要备份系统数据库，如对Master，Model，Msdb（TempDB不需备份）进行完整备份
1、还原Master数据库如果系统配置丢失或Master出现问题，可以进入单用户模式进行还原；如果出现下列情况，必须重新生成损坏的 master 数据库：A. master 数据库的当前备份不可用。B. 存在 master 数据库备份，但由于 Microsoft SQL Server 实例无法启动，因此无法还原该备份。1、重新生成 master 数据库：注意：在 SQL Server 2005 中已废止 Rebuildm.exe 程序。若要重新生成 master 数据库，请使用 setup.exe。1、 Start /wait setup.exe /qn INSTANCENAME=REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=例：start /wait e:“setup.exe /qn INSTANCENAME=mssqlserver REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=abc123@!@注：INSTANCENAME：指定实例名，默认实例则用mssqlserver表示REINSTALL：指定引擎SAPWD：强密码Setup.exe：指定光盘1中的根目录下的文件/qn 开关用于取消所有安装程序对话框和错误消息。如果指定 /qn 开关，则所有安装程序消息（包括错误消息）都将写入安装程序日志文件。有关日志文件的详细信息，请参阅如何查看 SQL Server 2005 安装日志文件。指定 /qb 开关将显示基本的安装程序对话框。还会显示错误消息。2、 还原Master备份(在恢复master的备份时要注意：必须在单用户(single user)模式下进行 ) a.进入单用户模式的方法： 1.在命令行模式下输入sqlservr -c -f -m或者输入sqlservr -m 其中：-c 可以缩短启动时间，SQL Server 不作为Windows NT的服务启动 -f 用最小配置启动SQL Server -m 单用户模式启动SQL Server2.系统默认没有设置PATH，先进入CMD，进入“C:“Program Files“Microsoft SQL Server“MSSQL.1“MSSQL“Binn”，执行SQLSERVR.EXE –M打开SSMS工具，先断开连接，再新建查询，执行以下命名还原USE masterGORESTORE DATABASE masterFROM disk=‘c:“master.bak‘GO开始实验了~~~MASTER重新生成为了模拟MASTER数据库坏了,我们就删除MASTER数据库(要停止SQL SERVER服务才能删除)在我的电脑,C:“ PROGRAM FILES“MICROSOFT SQL SERVER“MSSQL.1“MSSQL“DATE 中删除MASTER.MDF重新生成MASTER1.首先在CMD中输入start /wait setup.exe /qn INSTANCENAME=REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=其中的 SETUP.EXE为启动光盘的路径,我这里的是 E:“SETUP.EXE /QN我这里的为默认实例所以其中的用 MSSQLSERVER代替最后的为密码我设密码为abc123@!@,所以就用abc123@!@代替若没有返回错误,我们就可以到我的电脑中C:“ PROGRAM FILES“MICROSOFT SQL SERVER“MSSQL.1“DATE上面又有MASTER.MDF了接下来进入目录单用户模式启动SSMS工具,新建查询输入命令启动服务(MSSQL SERVER)打开SSMS连接到数据库后就可以发现数据库中的数据又回来了以下是我在命令行下搞的命令,我用记事本的方式全部复制下来了,为了不传附件,我就直接粘贴到下面了Microsoft Windows [版本 5.2.3790](C) 版权所有 1985-2003 Microsoft Corp.C:“Documents and Settings“Administrator>start /wait e:“setup.exe /qn INSTANCENAME=mssqlserver REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=abc123@!@C:“Documents and Settings“Administrator>cd C:“Program Files“Microsoft SQL Server“MSSQL.1“MSSQL“BinnC:“Program Files“Microsoft SQL Server“MSSQL.1“MSSQL“Binn>sqlservr.exe -m2008-04-18 15:47:46.56 Server Authentication mode is MIXED.2008-04-18 15:47:46.59 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)Oct 14 2005 00:33:37Copyright (c) 1988-2005 Microsoft CorporationEnterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)2008-04-18 15:47:46.59 Server (c) 2005 Microsoft Corporation.2008-04-18 15:47:46.59 Server All rights reserved.2008-04-18 15:47:46.59 Server Server process ID is 3984.2008-04-18 15:47:46.59 Server Logging SQL Server messages in file ‘C:“Program Files“Microsoft SQL Server“MSSQL.1“MSSQL“LOG“ERRORLOG‘.2008-04-18 15:47:46.59 Server This instance of SQL Server last reported using a process ID of 764 at 2008-4-18 15:46:40 (local) 2008-4-18 7:46:40 (UTC). This is an informational message only; no user action is required.2008-04-18 15:47:46.60 Server Registry startup parameters:2008-04-18 15:47:46.60 Server -d C:“Program Files“Microsoft SQL Server“MSSQL.1“MSSQL“DATA“master.mdf2008-04-18 15:47:46.60 Server -e C:“Program Files“Microsoft SQL Server“MSSQL.1“MSSQL“LOG“ERRORLOG2008-04-18 15:47:46.60 Server -l C:“Program Files“Microsoft SQL Server“MSSQL.1“MSSQL“DATA“mastlog.ldf2008-04-18 15:47:46.62 Server Command Line Startup Parameters:2008-04-18 15:47:46.62 Server -m2008-04-18 15:47:46.64 服务器 SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.2008-04-18 15:47:46.67 服务器 Detected 1 CPUs. This is an informational message; no user action is required.2008-04-18 15:47:46.78 服务器 Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.2008-04-18 15:47:46.84 服务器 Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.2008-04-18 15:47:47.95 服务器 Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction oordinator (MS DTC).This is an informational message only. No user action is required.2008-04-18 15:47:47.96 服务器 Database Mirroring Transport is disabled in the endpoint configuration.2008-04-18 15:47:47.96 spid5s Warning ******************2008-04-18 15:47:47.96 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required.2008-04-18 15:47:47.99 spid5s Starting up database ‘master‘.2008-04-18 15:47:48.17 spid5s recovery is writing a checkpoint in database ‘master‘ (1). This is an informational message only. No user action is required.2008-04-18 15:47:48.28 spid5s SQL Trace ID 1 was started by login 'sa'.2008-04-18 15:47:48.32 spid5s Starting up database ssqlsystemresource‘.2008-04-18 15:47:48.76 spid5s Server name is ‘VM01‘. This is an informational message only. No user action is required.2008-04-18 15:47:48.78 spid8s Starting up database ‘model‘.2008-04-18 15:47:49.20 服务器 A self-generated certificate wa
