harnal
asked on
collation change on system tables
We just moved some databases to a new server install. Somehow in the process the tables: TempDB, MSDB, Model collation changed. We would like to restore the collation back to Latin1_General_BIN
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> You want to change master database, it is also with wrong collation?
SQL Server installation includes instmsdb.sql for msdb rebuilt, and it is updated by later service packs in local install folder. Rebuilt of master database uses this script to create msdb database.
If you don't need to rebuld master, you can:
1. Install a new SQL Server on some other machine.
2. Apply latest service packs on both systems
3. Backup msdb and model databases
4. Restore them using the method from the post above
SQL Server installation includes instmsdb.sql for msdb rebuilt, and it is updated by later service packs in local install folder. Rebuilt of master database uses this script to create msdb database.
If you don't need to rebuld master, you can:
1. Install a new SQL Server on some other machine.
2. Apply latest service packs on both systems
3. Backup msdb and model databases
4. Restore them using the method from the post above
ASKER
The problem started when we used SQL lightspeed to do a restore. We didn't know SQLLS uses a different collation set. Looks like we're going to attempt to rebuild master, and go from there. If it needs a reinstall, so be it.