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?
asked Jun 06 '12 at 07:21 AM in Default
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 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.
answered Jun 06 '12 at 02:29 PM