March 3, 2009 at 3:54 am
Hi,
I am new to this forum and hoping you guys may be of assistance. I have been asked to backup and restore a sqlserver 2000 database which sits on an old Win NT server and has collation of sql_latin1_general_cp1_ci_as to a win 2003 server which has an existing instance with a collation of latin1_general_ci_as. This is for the apps guys to test code etc, will this be problematic/possible or is it advisable to ask them to find a more compatable server?
March 3, 2009 at 7:21 am
I think that when you restore a database into other server, the database takes its collation from master database, not sure tough, but i am using different collation for my test environment ( that was how i got my test sever), i dint face any problem,
On the other hand, try restoring in other database name and see how this affects, its a test server, play with it mate 🙂
March 3, 2009 at 10:07 am
you should be able to restore it no problem and its collation will be maintained. The problems arise when you start querying or joining to other databases on the server which have columns of incompatible collations. Often you need to alter your code or change the entire database collation. Also, creating hash temp tables can be an issue as these are created in the temppdb which will be a different collation to your DB. You may be lucky and not experience any issues. Its a good idea to standadise on a collation. I have spent many hours rebuilding servers because of this issue. Often due to developers designing databases on their workstations and then migrating them to a live environment where the collation differs.
thanks
SQL_EXPAT
March 4, 2009 at 11:09 am
Starting with SQL 2000 you can attach/restore a database with a different collation than the installed SQL Server. In other words, you may have three databases installed on the same SQL Server and they all can have different collations. Not optimal if you do joins but they will function.
March 5, 2009 at 2:13 am
thanks Guys,
I spoke to the app guy and it was established that they only wanted the structure of the database copied not the data, so I created a script from the original database, copied this over and changed the collation to be the same as the instance I was putting it onto.
Save the old database in a script, selecting all objects, users etc
All tasks – Generate SQL script – Show all button, General tab - tick all the objects, save script)
Run that script on the new database, which creates the database, users etc.
(Open sql script in notepad, copy all, go to destination database – Tools - SQL Query Analyser – paste in text and run)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply