Restore Collation Change

Using SQL Server 2005.

When I restore a database it changes the collation setting for the new
database.  I am simply backing up and restoring the DB with a new name to
the same server for comparison purposes.

The new database has the collation of the server.

Is there a way to restore and preserve the collation setting?

That should not happen, it should be impossible. How do you determine the co
llation of the restored
database? Below scrip show that a restored database will keep the collation:

CREATE DATABASE x COLLATE Albanian_BIN
GO
SELECT name, collation_name from sys.databases where name = 'x'

BACKUP DATABASE x TO DISK = 'C:\x.bak' WITH INIT
GO
RESTORE FILELISTONLY FROM DISK = 'c:\x.bak' WITH FILE = 1
GO
RESTORE DATABASE y FROM DISK = 'C:\x.bak'
WITH
MOVE 'x' TO 'c:\x.mdf'
,MOVE 'x_log' TO 'c:\x.ldf'
GO
SELECT name, collation_name from sys.databases where name IN('x', 'y')


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Joe L" <jjj@lll.com> wrote in message news:ueRjgD8CGHA.4004@tk2msftngp13.phx.gbl...
> Using SQL Server 2005.
>
> When I restore a database it changes the collation setting for the new dat
abase.  I am simply
> backing up and restoring the DB with a new name to the same server for com
parison purposes.
>
> The new database has the collation of the server.
>
> Is there a way to restore and preserve the collation setting?
>
>
>
>