Rebuild Master

This happens a lot of times that you install your Sql Server with a particular collation and find that the database you need to have on the server requires a different collation. At first this seems to be pretty simple. I thought I would just go to the Server properties and change it. I made a confident move for it only to find that the collation cannot be edited from the properties. I browsed through a few online forums which suggested re-installing the Sql Server. I was not willing to give re-installation a try (Installing the MS Sql Server really makes you develop ardent love for cloud services). So I asked a few peers around and then eventually the IT guys and all of them suggested a re-installation. Re-installing the Sql Server is a cumbersome task. Also the collation property box (though inactive) would not let me think that there was not a simpler way to do it. After a little searching I finally found a simpler way.

This can be done by rebuilding the master databases on the server. This would remove all the user databases on the server and so back-ups must be taken before you try to restore. This seems like an overhead but creating a DB back up takes less time than going for a full fledged installation. However, it depends on the database size. Once the back-ups are in place, run an instance of the Sql Server setup but from the command prompt with a few parameters to restrict the setup operations only to restoring of master databases. Such an instance of Sql server setup takes much less time to complete than the familiar one (took less than a minute for me).

So here is what you do : Open the command prompt and navigate to the folder containing the Sql Server 2008 setup. Then give the setup command in the following way :

setup.exe

/QUIET

/ACTION=REBUILDDATABASE

/INSTANCENAME=< instance_name>

/SQLSYSADMINACCOUNTS=< account>

/SAPWD=< password>

/SQLCOLLATION=< collation_name>

Here < instance_name> is the instance name of your current sql server (MSSQLSERVER in my case). <account>  is the user group which will have security to access the database ( something like user1@domain.com) . < password> is the server access password in case you want to enable both sql server and windows authentication access. Finally < collation_name> is the name of the new collation you want to go with. Make sure you do not have spaces around the ‘=’ sign.


This would run the setup to build the master databases again with the given collation and other parameters. It takes very less time and thereafter you can restore the user DB back-ups that you saved. If something goes wrong you can refer to a summary sheet created during the setup run which in my case was at the path C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log.

This seems simple enough to me. Hope this saves you time someday.