question

Etienne avatar image
Etienne asked

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?
sql-server-2008sql-server-2005upgrade
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Etienne avatar image Etienne commented ·
Please note that I want to just move some tables, not the WHOLE DB.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
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.
0 Likes 0 ·
Etienne avatar image Etienne commented ·
Only 1 at a time as planning progress.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

basit 1 avatar image
basit 1 answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.