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...eagreen">
> 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?
>
>

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 collation 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 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?
>
>

Restore

I've backed-up a database with empty tables and then restored it to a new
database. Everything restored OK, apart from the tables, which are not
created. I'd expect it to create the tables, even though they are empty.
Am I doing something wrong?
Possibly ownership problems and orphaned users. Check out sp_change_users_login in Book sOnline.
Also check if the database owner is orphaned.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"HB" <HB@.discussions.microsoft.com> wrote in message
news:C98EAFC0-13F8-41F8-825D-1F25CA2D7E56@.microsoft.com...
> I've backed-up a database with empty tables and then restored it to a new
> database. Everything restored OK, apart from the tables, which are not
> created. I'd expect it to create the tables, even though they are empty.
> Am I doing something wrong?