
	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

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

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.

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.

Digg This!

StumbleUpon

Del.icio.us

RELATED CONTENT

	SQL Server Business Intelligence (BI) and Data Warehousing
	Using the Pivot transformation in SQL Server Integration Services
	DBA career paths could lead to business intelligence
	Are data warehouses made for the cloud?
	Q&A: Business intelligence gets a facelift in SQL Server 2008 R2
	Project Gemini gets a new name, Madison earns buzz
	Speed up reports in SQL Server Reporting Services with caching
	Data Transformation Services vs. SSIS: The key differences
	Using package configurations in SQL Server Integration Services (SSIS)
	How SQL Server 2008 components impact SharePoint implementations
	Achieving high availability and disaster recovery with SharePoint databases

SQL Server Stored Procedures

SQL Server Mailbag: CALs, witnesses and unwanted changes

SQL Server Mailbag: Stored procedures, triggers and SSRS reports

Top tips and tricks for SQL Server database development

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 Migration Strategies and Planning

New SQL Server 2008 R2 CTP set for November

PASS Summit 2009 Preview

Are data warehouses made for the cloud?

Q&A: Moving forward with SQL Server in the cloud

SQL Server Mailbag: Migrating down to Standard Edition

Microsoft releases SQL Server 2008 R2 CTP

A first look at Microsoft SQL Server 2008 R2

Using Microsoft Hyper-V for SQL Server consolidation

Migrating to SQL Server 2008 and leveraging new features

The challenges of SQL Server consolidation

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

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
Operating Systems IT Management Networking Database	Security Servers and Storage Applications and Development Career Center

SQL Solutions - SQL Database Design

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