The method of rebuilding master and other system databases in Sql server 2008 has changed. It does not use rebuildm.exe as it was the case in Sql Sever 2000
To rebuild the master database in SQL 2008, you need to use setup.exe. There is no need to use the installation DVD or the installation files. All the required files should be available on the server itself.
On a normal installation, the setup.exe can be found at
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release
Note; before rebuilding master, it is recommended to take a backup of the existing system databases in case your rebuild is not what you wanted so that you can go back to the previous configuration. Also, before you start rebuilding the master, you need to;
- Detach all the user databases so that they can be re-attached after the rebuild
- Script all the logins
- Script all the jobs
There is a good blog with sample scripts on how to do the above in http://www.mssqltips.com/tip.asp?tip=1531
and
http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx
If you are rebuilding the master in a clustered environment,
Make sure that the node that you are rebuilding the master from is the owner of the instance.
Open a command prompt and go to the directory where the setup.exe is located; which typically is in
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release
Then type the following substituting the parameters to your environment.
setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=MyDomain\adminUser /SAPWD=9Cazpop1Z123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
Make sure there are no spaces between the “/” and the parameter otherwise you will get the following error.
“The syntax of the argument “//” is incorrect”
I was running two instances of sql server on the cluster (active-active) so I had to rebuild the master twice. Once for each instance as I wanted to change the collation of the master database on both instances.
You will have to take the sql server resource offline in a cluster other wise you will get the following error.
“If this clustered instance is owned by the current node, the sql server resource must be offline or failed.”
If the Sql server instance that you are trying to rebuild the master for is not owned by the node that you are running the serup.exe command from, you will get the following error:
“The Sql Server resource group must be owned by the current cluster node. Setup requires access to shared cluster resources.”
To fix this, you need to move the cluster resource to the node that you are running the setup.exe from
To take the Sql Serve resource offline, open Failover cluster manager, click on the Sql Server instance (or group as it used to be called) then under Server name on the right hand pane, tight-click and select take resource offline as show in the snapshot below.
After the setup.exe completes, bring the Sql resource online again and start attaching your databases, re-create logins, jobs..etc.
After completing the rebuild on the first node, I had to repeat the same process on the other node substituting the instance name in the command below.
setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=SNAMESSQL01 /SQLSYSADMINACCOUNTS=MyDomain\adminUser /SAPWD=9Cazpop1Z1234 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
Rebuilding the master, recreated the tempdb in the default location. If you are keeping tempdb in a different location perhaps on another set of disks then you need to move it after you start the sql server resource,
In order to move tempdb, use sp_helpfile to find the name of the tempdb files
Then
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\tempdblog.ldf')
In order to verify that the collation has changed for the master databases, use
sp_helpdb
or
SELECT DATABASEPROPERTYEX('tempdb', 'Collation')
