SQL Server 2012笔记分享-39：重建master数据库

===================================================================

（一）重建master

   

当master数据库被破坏或者SQL server服务无法启动时，我们可能需要重建master，具体可以参考下面的英文注释信息。

首先对master数据库做好数据备份。

其次我删除master里面的系统表，模拟master崩溃。

Rebuilding the Master Database

You will need to rebuild an entirely new version of the master database if:

? The master database is severely damaged, and the SQL Server instance will not restart.

? A current backup of master is not available.

To rebuild the master database, run the SQL Server setup from the command prompt. The setup.exe executable is used to install SQL Server, upgrade from previous SQL Server instance, uninstall SQL Server, and to rebuild the master database. The attributes used for using to rebuild the system databases with descriptions are as follows:

? /QUIET – Specifies that the setup will not display

? /ACTION=REBUILDDATABASE – Specifies a setup workflow, such as INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. In this case, it is being used to rebuild the master database.

? /INSTANCENAME=<instance_name> – Specifies a default or named instance. MSSQLSERVER is the default install for non-Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).

? /SQLSYSADMINACCOUNTS = <accounts> – Specifies the Windows account(s) to provision as SQL Server system administrators.

? /SAPWD= <password> – Specifies the password for the sa, sysadmin account.

? /SQLCOLLATION=<collation_name> – Specifies a Windows or SQL Server collation to use for the Database Engine

You can rebuild the system database for a default instance by using the following T-SQL syntax:

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=‘<instance name>’ /SQLSYSADMINACCOUNTS=‘<DomainName\Username>’ /SAPWD =‘<password>’

然后使用下面的命令对master数据库进行修复。下图中使用的是AD账户。

Once the rebuild is complete, you can restore your original version to the server if a backup is available, by using the steps to restore the master database. If no backup is available, you will need to manually re-create and reconfigure your system. Rebuilding the system databases includes rebuilding the msdb and model databases, so you should ensure that you have backup copies of your versions to restore, following the rebuild.

The log files from the rebuild can be found in the ..110\setup bootstrap\logs directory. The Summary.txt file will show from a high level whether the rebuild was successful and will point to more detailed logs if there were any errors. This file represents the most recent summary of an execution setup. You need to look for a folder inside the logs directory that matches the datetime when you run setup to rebuild the system databases.

重建完成后，可以看到master的系统表都回来了。

（二）恢复master备份

重建完成后，可以继续还原master的备份。

http://technet.microsoft.com/zh-cn/library/ms190679.aspx

首先要让数据库处于单用户模式，可以修改SQL server的服务，在启动参数加上-m;

然后再进入sqlcmd里面，输入下面的master还原的命令。

sqlcmd

1>restore database master from disk='d:/fullbackup/fullbackup.bak' with replace;

2>go

注意：

   

如果当初安装SQL2012的时候是RTM版本，然后升级到了SQL 2012 SP1，那么在重建master数据库的时候要使用SQL 2012 SP1的安装光盘，如果你使用SQL 2012 RTM的镜像光盘来重建SQL 2012 SP1的master数据库，那么在恢复master备份的时候会失败，会提示master版本和备份中的版本不一致。

==================================================================

下面是一篇重建master的文章

http://www.cnblogs.com/qanholas/archive/2012/10/24/2736641.html

====================================================================

2014年7月18日更正

在服务的启动模式添加M参数，正规的方法应该是打开SQL server configuration manager里面停用SQL server服务，然后再加参数，最好不要像上面文中写的直接在services.msc服务管理器中修改。

=====================================================================