Alter database model COLLATE SQL_Latin1_General_CP1_CI_AS

Hi folks,

I do not have a backup of the model database and have had to rebuild SQL Server 2000 once already. All my databases except model and tempdb have the collation SQL_Latin1_General_CP1_CI_AS those two have Latin1_General_CI_AS.

During SQL rebuild I've restored over master and msdb and they work fine. Only came across problem with stored procedure and I'm lost as to how to alter model without a backup of it.

Any easy way to change model collation?
The command listed in subject fails as it is a system table.

Is it as simple as going into single user mode and trying that command again?

Help please this is day 3 of my little nightmare.

Check the tool rebuildm.exe It is supposed to rebuild master and the other ystem DBs.
But watchout: You will loose all your user DBs and will have to restore them afterwards

rebuildm.exe does create a new shell of sql aka the master,msdb and model databases. However when we restore master it then has the original collation, we do the same for msdb and it also has the original. My error was not to back up the model database. As such I appear to be toast. Anyone else have a way around this? Is their a model.mdf and model.ldf with SQL_Latin1_General_CP1_CI_AS ? :mad:

We took a similarly configured server, Same SQL version and service pack with similar hardware setup, created a backup of that SQL boxes model database and copied it over to the SQL box in trouble.

Once we had backed up the master, msdb and model of that box we did a restore of the rogue model database. Hey Mikey the SQL box liked it. Not only did it like it it changes tempdb as expected and all appears to be fine in toytown.

Hopes this proves to be helpful to someone else someday, also helps this continues to work without recourse. Have a good day folks. TCD