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.

Enable xp_cmdshell

With SQL Server 2005, xp_cmdshell is disabled by default

To enable this feature:

Option 1: Using T-SQL

--Enable the feature
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE

Option 2: SQL Server 2005 Surface Area Configuration tool

From Surface Area Configuration Tools -> Choose “Surface Area Configuration for Features” > Check “Enable xp_cmdshell”