Change SQL Server Collation without rebuilding system databases

How many of you want to change the SQL Server Collation without rebuilding the system databases?

In this post, I will explain a method using which the server collation can be easily changed and is through a undocumented start up parameter.
Since this is undocumented, I highly recommend to use this with extra care and with your own risk.

How to Restore model and msdb database

Unlike master database, restoring model and msdb is simple and follows the same procedure as restoring any other user database. However we have to be very cautious while restoring system databases as it will have sensitive data which is important for SQL Server to function without any issues.

How to Restore Master database

Before restoring master database, make sure

1. The build versions of the both instances (Current instance and the instance where backup is generated) should be the same.
• More Information at http://www.sanssql.com/2013/09/the-backup-of-system-database-on-device.html

The backup of the system database on the device <backupPath> cannot be restored because it was created by a different version of the server <version> than this server <version>

While I was trying to restore the backup of master database taken in SQL Server 2008 on to an SQL Server 2008 R2 instance, I was presented with the below error.

Msg 3168, Level 16, State 1, Line 1 The backup of the system database on the device D:\Backup\master.bak cannot be restored because it was created by a different version of the server (10.00.1600) than this server (10.50.1600). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.