Home > Ask the SQL Server Experts > Archive: Performance Tuning Questions & Answers > Change collation to SQL Server 2005 after upgrading databases
Ask The SQL Server Expert: Questions & Answers
EMAIL THIS

Change collation to SQL Server 2005 after upgrading databases

Jeremy Kadlec EXPERT RESPONSE FROM: Jeremy Kadlec

Pose a Question
Other SQL Server Categories
Meet all SQL Server Experts
Become an Expert for this site


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


>
QUESTION POSED ON: 14 December 2007
One of my clients is running a 7GB database in SQL Server 2000 and they're rolling everything out to SQL Server 2005. Basically, I need to copy the database from 2000 to 2005, but change the collation to the SQL Server 2005 standard.

So far I have been creating new databases (test), scripting the old databases and applying the scripts before importing the data via Data Transformation Services (DTS). This did not work, so I split the script up (tables, views, sp's, fk's etc). However, the scripts generated, call stored procedures, views or tables before they've been created and there always seems to be some views that get missed.

Can you suggest a smoother approach? I have read your article Step-by-Step Guide: Migrating to SQL Server 2005 with Copy Database Wizard, but it doesn't mention collation.


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


RELATED CONTENT
SQL Server Business Intelligence (BI) and Data Warehousing
Ensuring high availability of SSAS databases
Building a data warehousing and BI solution
An overview of SQL Server Report Builder 2.0
An introduction to SQL Server data warehousing concepts
Sharing SSAS cube data in SharePoint with PerformancePoint Server 2007
Microsoft SQL Server Integration Services primer
A short history of SQL Server Integration Services
SQL Server Reporting Services Fast Guide
New data profiling tools in SQL Server 2008
Utilize SSAS for data predictions and classification using Excel

SQL Server Stored Procedures
Top 10 SQL Server development tips of 2008
SQL Server trigger vs. stored procedure to receive data notification
SQL Server errors, failures and other problems fixed from the trenches
SQL Server and data manipulation in T-SQL
How to use SQL Server 2008 hierarchyid data type
SQL Server stored procedures tutorial: Write, tune and get examples
Check SQL Server database and log file size with this stored procedure
SQL Server source code analysis and management adds database security
Configure SQL Server Service Broker for sending stored procedure data
Find size of SQL Server tables and other objects with stored procedure

SQL Server Migration Strategies and Planning
Using Microsoft Hyper-V for SQL Server consolidation
Migrating to SQL Server 2008 and leveraging new features
The challenges of SQL Server consolidation
Testing a SQL Server environment before an upgrade
SQL Server Consolidation Fast Guide
SQL Server consolidation strategies and best practices
Does upgrading to SQL Server 2008 fit your business?
A guide to advanced new features in SQL Server Management Studio 2008, part 2
A guide to basic new features in SQL Server Management Studio 2008, part 1
SQL Server virtualization pros and cons: Weigh the performance impact

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
data aggregation  (SearchSQLServer.com)
data preprocessing  (SearchSQLServer.com)
data warehouse  (SearchSQLServer.com)
FileMaker  (SearchSQLServer.com)
GIS  (SearchSQLServer.com)
MOLAP  (SearchSQLServer.com)
pivot table  (SearchSQLServer.com)
Quiz: SQL Server 2000  (SearchSQLServer.com)
SQL  (SearchSQLServer.com)
T-SQL  (SearchSQLServer.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary


Depending on the collation change (binary to non-binary or vice versa), the best approach with native tools would be to script out the objects and then migrate the data via DTS or SQL Server Integration Services. Just be careful to make sure the collation is correct at the database, table and column levels when you are finished.




Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice



SQL Solutions - SQL Database Design
HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Site Map




All Rights Reserved, Copyright 2005 - 2009, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts