Collation conflict isssue

[John Jayaseelan]

Hi,

The tempdb db is having different collation than the application db.

I rebuilt the master db with the appropriate collation after backing up
master, model, msdb, appln databases.

On restore of the databases (master, model, msdb, appln) from the backup
restores the backed up database collation.

Is there any idea to restore the db with different collation than the
backed up collation.

Thanks,







*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Re: Collation conflict isssue

John Jayaseelan (john.jayaseela n@caravan-club.co.uk) writes:[color=blue]
> The tempdb db is having different collation than the application db.
>
> I rebuilt the master db with the appropriate collation after backing up
> master, model, msdb, appln databases.
>
> On restore of the databases (master, model, msdb, appln) from the backup
> restores the backed up database collation.
>
> Is there any idea to restore the db with different collation than the
> backed up collation.[/color]

The only way to change the collation of a database, is to bulk out
all data, and rebuild the database from scripts. Well, you could
alter the collation of all character columns, but that would require
you to drop indexes and constraints, so that would probably be more work.

The same applies to the system databases. Rather than restoring backups
of these, you should have entered the information by other means.

Another option may be to install a second instance with a matching
collation.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp