September 10, 2012 at 12:14 am
Hi All,
I am waiting for project in whcih I need to upgrade sql server 2000 to 2008R2. The main thing is collation going to be changed.
It will be a side by side migration. Apart from this, I do not have any more details).
I plan to do scripting source objects and bcp them to destination.
Can anyone please advise how to do (better option) this upgrade with collation change?
Thanks
September 11, 2012 at 6:17 am
If you are saying SQL Server for one instance is different for the other collation wise then the easiest way (if the SQL2008R2) if it is not being used simply re-install it. The only other way is to rebuild master which will cause you to loose every login and everything as it redoes master, model, msdb and TEMPDB.
September 11, 2012 at 8:13 am
As it is a side-by-side migration, what you need to do is start testing your data to make sure the collation is compatable and translatable before the migration starts.
You could restore the databases to the new server, but that won't change the database collation. You'd need to actually go through each database and run the collation change through the ALTER DATABASE command.
And you have to watch out for that individual table (and possibly column) collations that might get changed but should remain the with the old collation.
Look up collations, [SQL Server] about collations in Books Online. There's a lot of information there that you should know before you do this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply