We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SQL Server server/DB collation conflict when restore database

vinodj181
vinodj181 asked
on
Medium Priority
1,654 Views
Last Modified: 2012-05-06
I have an issue where my SQL server and database collation conflict.
The database collation is correct: "SQL_Latin1_General_CP1_CI_AS"
The server is incorrect "Latin1_General_CI_AS"

I have uninstalled SSQL server and reinstalled and the server collation installs correctly as: ""SQL_Latin1_General_CP1_CI_AS"

If I install a default database we use the server and DB collations match ok.

My issue is when I reattach the required database or restore a backup the system collation changes back to "Latin1_General_CI_AS"???!!

I can't work out why this would happen and I need help to stop this occurring?

I have even tried reattaching with a new database name. So there must be something in the database I am restoring that is changing the system collation?

I haven't tried importing the database yet instead of reattaching or restoring? Not sure if this would work? I have issues with downtime issues whilst trying to fix this to contend with as its a production environment and so hope my last attempt can fix it if I get the right advice.

Your help is much appreciated.
Comment
Watch Question

Commented:
The server collation is typically stored in the master database and can normally only be changed using the rebuildm (rebuild master) utility..

If a user database does not match the server collation, you can use the following command to change the collation of the user database -

ALTER DATABASE <dbname> COLLATE SQL_Latin1_General_CP1_CI_AS

It is a good practice to have the same collation as a server/database wide setting as the tempdb inherits the collation from the master/model database and if temp tables are used in procedures without explicitly declaring the collation for char/varchar fields, you could end up with problems..

Author

Commented:
The server collation is correct after a reinstall of sql server - SQL_Latin1_General_CP1_CI_AS and installing a default database they both match using query:

select 'BPRODATA',convert(sysname,DatabasePropertyEx('BPRODATA','Collation'))
union
select 'SERVER',SERVERPROPERTY ('collation')

When I restore the database backup or reattach it when the query above is re-run  the system changes back to "Latin1_General_CI_AS" but the database collation is correct - SQL_Latin1_General_CP1_CI_AS

Is the collation type being stored in the backup? How does the system collation type change? Do I need to run the "rebuildm (rebuild master) utility.."?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Yes. Collation type as of master will be stored in your backup files.

Try this approach and should help you..

1. In your current server detach all your databases.
2. Reinstall SQL Server 2005.
3. Attach all the databases.

Detach and Attach will not consider those collations as far as I know.

Author

Commented:
I only have one database file BPRODATA and Ihave doenteh above. do I need to unattach and reattach master as well? This gets installed with sql does it not?

Author

Commented:
the collation of system only is changing when i reattach my database? the database collation is correct.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
I hope you have to do backup and restore of your master database too.

Once you install your SQL Server with the changed collation, take a backup of your master database, then backup your database.

This will change the collation of your server to old one.
Then restore the master database to obtain the desired collation.

Check this out for restoring master database:

http://technet.microsoft.com/en-us/library/ms175535.aspx

Author

Commented:
Thanks I will try this in the morning. We don't normally do a backup of the master database. It appears that this should be common practice.

Author

Commented:
I am getting issues with needing to be in single user mode to restore the master database. Do you have info on changing this and then back again?

Author

Commented:
I am trying to start sql in single user mode as per this page using command prompt
cd \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
sqlservr.exe -m
http://msdn.microsoft.com/en-us/library/ms180965(SQL.90).aspx

but I still get the error "To restore the master database, the server must be running in single user mode..." I cannot seem to change it to login as single user mode?

Author

Commented:
Ok after trying what has been recommended it appears my issue seems to be that when I do a fresh install of sql my master and system databases are now being installed with collation of "Latin1_General_CI_AS" when the database I have has collation "SQL_Latin1_General_CP1_CI_AS"

All regional settings are set to English (Australian) and so this should not be occurring. I reset all defaults to this regional rebooted and reinstalled sql and now the collation of the install seems to be the issue now. This did not appear to be occurring previous to this regional reset for all users and reboot so I'm assuming something is askew in windows?

Is it possible that something in windows is causing the install to use the wrong sql install collation? Is there something I can check to see what regional setting it is trying to use in registry and change it?
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
No.. I hope it will show you some default collation based upon your regional settings.

"Instead of changing the default collation of an instance of SQL Server, you can specify a default collation for each new database you create."

This is what I am able to see in many forums and approaches when trying to search for Changing Server collation.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
I ended up solving this. I had to change my regional to English (USA) reinstall SQL then restore my database.

That then made sure the system collation was "SQL_Latin1_General_CP1_CI_AS"
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.