• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1156
  • Last Modified:

SQL Server server/DB collation conflict when restore database

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.
0
vinodj181
Asked:
vinodj181
  • 8
  • 3
1 Solution
 
reb73Commented:
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..
0
 
vinodj181Author 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.."?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
vinodj181Author 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?
0
 
vinodj181Author Commented:
the collation of system only is changing when i reattach my database? the database collation is correct.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
 
vinodj181Author 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.
0
 
vinodj181Author 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?
0
 
vinodj181Author 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?
0
 
vinodj181Author 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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
vinodj181Author 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"
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now