x

Upgrade SQL 2005 table to 2008

I know there are different ways to upgrade a database from 2005 to 2008 but what is the best way if I just want to upgrade some tables from a 2005 DB to a 2008 DB?

By scripting the tables and then to use SSIS to move the data over?
more ▼

asked Jun 06, 2012 at 07:21 AM in Default

Etienne gravatar image

Etienne
70 3 3 5

Please note that I want to just move some tables, not the WHOLE DB.
Jun 06, 2012 at 08:41 AM Etienne

what percentage of the data/tables need to be moved? The backup/restore/truncate method may be quicker process than working how to only move the data that you want need.

SSIS is quick (and indeed repeatable) once the package is created but the selection of lots of tables can be tiresome.

SSIS will create the tables if you set it to so there is no need to prepare anything other than the destination database.
Jun 06, 2012 at 08:43 AM Fatherjack ♦♦
Only 1 at a time as planning progress.
Jun 06, 2012 at 08:45 AM Etienne
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

There are several ways you can effect an upgrade of a database, be it via mirroring, backup/restore, bcp, TSQL etc.

Personally I usually revert to the backup/restore option as it fits best with my needs and infrastructure here. I backup the database on the 2005 server and then restore it onto the 2008 server. As part of the restore the database is upgraded but still running in compatibility mode. Dont misunderstand this, the database will not go back to 2005 (you have the live database and the backup if you need to do this) but some of the 2008 functionality is not available until you actually set it to be a 2008 database.

Once you have done this you can simply truncate the tables that you dont need data in, if that is important to you.

[Edit] After some more info in comments
If you are aiming to move one table at a time then SSIS may be the best method for you as you can reuse the same package and simply add the new table each time to want more data moved. I hope its a small database or that process will become very repetitive!
more ▼

answered Jun 06, 2012 at 08:36 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left
if you need to upgrade only some table then you need to create the database in 2008 and then generate the script of those table in 2005 Database and execute in 2008 Database and then use Export import wizard to import the data.
more ▼

answered Jun 06, 2012 at 02:29 PM

basit 1 gravatar image

basit 1
499 52 64 85

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x1853
x69

asked: Jun 06, 2012 at 07:21 AM

Seen: 894 times

Last Updated: Jun 06, 2012 at 02:29 PM