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?
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.
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!
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.
9 People are following this question.
