restore adds collation to nvarchar columns

Question

restore adds collation to nvarchar columns

blom0344

SSCrazy

Points: 2073
More actions
September 2, 2009 at 8:05 am

#225662

When restoring a database backup to an SQL server 2005 instance (9.0.1399 patch) all nvarchar and ntext columns are build with a specified collation type. (that should not be there at all)
When restoring to 2 other SQL server instances (9.0.3077) the database is restored without specific collation types on columns. (like we expect and appreciate 🙂 )
The database has collation type Lithuanian_CI_AS, in 2 schema's this is used for column collation, but the third schema throws Latin1_General in our face.
Is this an early 2005 bug of some kind. am I missing something.
Please , your input is appreciated

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

wierd; I've always expected that restore gives the exact same data every time...restore it and the collation, which is in the databases sys.columns is restored to wherever it was.

I haven't seen collation switches..intuitively it doesn't make sense.

You said it's the exact same backup, right? it's not two different backups being restored to your different machines?

on the machine that you restore that seems to change the collation,if you rin this query, are any of the collations NULL?

select type_name(system_type_id),collation_name,* from sys.columns

where type_name(system_type_id) IN('varchar','nvarchar','char','nchar','text')

my only theory is that if it was null, it might take the server or database collation instead....dunno, can't seem to recreate it so far.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

When you restore a backup of a database to a different server all string columns will keep the collation of the original database. In your case I suspect that the server with nuild 1399 has a different server collation than the other two.

You can check this by running the following query:

SELECT SERVERPROPERTY('Collation')

[font="Verdana"]Markus Bohse[/font]

blom0344 SSCrazy Points: 2073 More actions · Answer 3

MarkusB (9/2/2009)
When you restore a backup of a database to a different server all string columns will keep the collation of the original database. In your case I suspect that the server with nuild 1399 has a different server collation than the other two.
You can check this by running the following query:
SELECT SERVERPROPERTY('Collation')

To be exact:

Latin1_General_CI_AS (Server - build 1399 - where backup is restored)

Lithuanian_CI_AS (Database as created by developers)

Lithuanian_CI_AS (Schema 1; alle nvarchar, ntext)

Lithuanian_CI_AS (Schema 2 ; alle nvarchar, ntext)

SQL_Latin1_General_CP1_CI_AS (Schema 3; alle nvarchar, ntext)

Obviously, conflicts arise when using objects from schema 1/2 with schema 3.

It is however beyond me, why these explicit collation settings exist for the string columns and WHY different ones between schema's?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Is is possible that the server you are having trouble with does not have the Lithuanian collations available?

blom0344 SSCrazy Points: 2073 More actions · Answer 5

Michael Valentine Jones (9/2/2009)
Is is possible that the server you are having trouble with does not have the Lithuanian collations available?

Checked this, but it does not seem to be the issue. We have a new backup version now from development that shows a third scenario:

2 schema's have explicit collation settings on columns and the third schema shows null values for the collation on columns. Anyway, this eliminates the conflict for now cause explicit and implicit collationtypes are now the same :hehe:

My initial posts mentions differences beteen EM versions. This is misleading ; the GUI shows different scripts for collation when generating a table script , whereas in both cases a check on syscolumns reveals that this is not the case....