vinodj181
asked on
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
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_C
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.
The database collation is correct: "SQL_Latin1_General_CP1_CI
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_C
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.
ASKER
The server collation is correct after a reinstall of sql server - SQL_Latin1_General_CP1_CI_
select 'BPRODATA',convert(sysname
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_
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.."?
select 'BPRODATA',convert(sysname
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_
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.."?
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.
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.
ASKER
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?
ASKER
the collation of system only is changing when i reattach my database? the database collation is correct.
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
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
ASKER
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.
ASKER
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?
ASKER
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?
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?
ASKER
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
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
That then made sure the system collation was "SQL_Latin1_General_CP1_CI
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_
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..