Results 1 to 4 of 4

Thread: Alter database model COLLATE SQL_Latin1_General_CP1_CI_AS

  1. #1
    Join Date
    Feb 2003
    Location
    Barrie, On, Canada
    Posts
    29

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    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

  3. #3
    Join Date
    Feb 2003
    Location
    Barrie, On, Canada
    Posts
    29
    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 ?

  4. #4
    Join Date
    Feb 2003
    Location
    Barrie, On, Canada
    Posts
    29

    Apparent Solution

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •