Greetings,

I'm restoring a database (Sql server 2008) which has a collation which differs from my SQL server installation, which causes issues when restored as a number of store procedures reference temporary tables.

As I want to restore it with the same collation as the server, In the past I used the method of re-creating the database from existing scripts and then using the data import wizard to copy all the data in from the restored database.

I was unable to restore the database and change database, table and column collations due to the constraints etc..,

Nor have I used BCP to copy database as am not overly familar with it.

Is my option of creating the database and using the data import the fastest way to ensure I have the database with the same as the server collation or is there a more efficient method?

On the same topic, I had issue with importing data from one table in the past where the import failed, I think it was a mismatch in datatypes. However when I restored the database with the wrong collation and simply inserted from one table to another (populated to the empty table) it worked successfully. It was 250,000+ rows I recall, has anyone else experience anything similar with the data import wizard?

Thanks

Rob

First off, you can search the forums for numerous discussions of collation differences and how to resolve them.  However, I suggest you back up a bit.  What exactly are you trying to accomplish?  The most productive route is to simply use the same collations between the 2 servers.  The amount of time you will waste trying to "import" your database and avoid the collation issue will be significant - especially if you do this on any basis (i.e., more than once). 

Second, perhaps the more productive route is to correct (yes - correct if we assume your primary issue is one of case-sensitivity) so that the code uses the exact name for object references.  I see over and over sql that constantly (frequently randomly) mixes upper and lower case in object (table and column) names - even within the same batch/procedure/function.  In addition, if the code is expected to be run in database server that can have different collations, then the code should be written to account for that fact. 

Lastly, you should be very careful about this proposed plan.  Changing collation can affect the way your database "works" - the code may give different results. 

Hi,

Thanks for the reply, I have one sql server installation to use which contains around 20 databases that share the same collation as the server.  Rarely if there is an issue I will request a database backup from a client, the one received has a different collation (They are from the same country so it's not a collation difference due to language, probably just using the default value of their sql server installation).

The sole reason restoring the database won't work with the software I need to test is because a small number of store procedures use temporary tables which are referenced in join clauses, which causes collation errors.

I don't control the code, and the majority of it has changed to ensure uses database default location unfortunately for me the particular area which has an issue uses store procs which uses temp tables.

I can see my current method as you point out is very time consuming, especially with larger databases.

I'm beginning to think time would be spent learning how to write scripts to change the collation of the database, tables and columns, dropping and creating constraints etc..,

As I said - you can search the forums since this is a relatively common question.  Perhaps it would be easier to install an instance where you can change the collation of the master and tempdb databases to match that of your client db, rather than the other way around.  As for the fastest way to import a database and change the collation - that depends on the size of the database and the complexity of the schema. 

msdn - rebuild system database