Upgrade to 2008R Collation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Upgrade to 2008R Collation

Upgrade to 2008R Collation

Rate Topic

Display Mode

Topic Options

Author

Message

Posted Monday, September 10, 2012 12:14 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:07 AM
Points: 327, Visits: 532

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

Post #1356567

Markus

Posted Tuesday, September 11, 2012 6:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 1,059, Visits: 2,261

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.

Post #1357349

Brandie Tarvin

Posted Tuesday, September 11, 2012 8:13 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 6,717, Visits: 5,788

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.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1357451

« Prev Topic | Next Topic »

Permissions