Sunday, December 13, 2009

SQL Server Lever collation change

1. Backup all databases.(both system and User)

2. If applicable, backup DTS packages, Jobs, SSIS packages, mailing profiles or other objects installed by the application.

3. Script/take screenshot of logins with level of access on server level as well as the database level, user objects for example - tables, functions, types and stored procs, extended stored procs, if any on model or master db.

4. Detach all the user databases.

5. Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI


6. Check the tempdb collation and it should be ?Latin1_General_CI_AS? create users with help of screenshot/Script and restore user objects from step 3. Run scripts for functions and types if any.

7. Attach user databases from step 4 / Restore all the databases from the backup from step 1 and all the objects from the backup taken in step1. Refer client mail for further clarifications.