Wednesday, January 31, 2007

Import/Export between SQL 2005 and 2000

Due to architectual differences, you can not restore a database backup from higher version to Lower version [SQL 2005 to SQL 2000]. Once it is attached or restored to a 2005 instance it can not go back intact.

One question which comes into mind immediately is, what if DB is set to SQL 2000 compatibility level? Compatibility level is for client connection behavior. On-disk it is still SQL 2005.
One solution is to create the database manually, create objects and use SSIS/DTS/BCP to transfer the data.

No comments: