Restoring SQL 2005 DB into a SQL 2000 DB

[darronb]

Is it possible to Restoring SQL 2005 DB into a SQL 2000 DB, and if so how do you do it as I am getting an error back every time I try?

Thanks

 

[bborissov]

Nope, you can't.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

You can not restore a version of DB saved in the newer format (even in compatibility mode) into the older version of SQL Server.

 

[JoeAtWork]

I've done it by creating a script to recreate the tables in the database (you should be able to create the script from within Enterprise Manager). In my case copying all the other objects (Indexes, etc.) was not so important, but if you want the full database you will want to script all the other stuff as well. If you have not used features that were only introduced in 2005, this may work.

Here's the script and instructions that I used (note, I only needed one table):

-- 1. Create the blank database in SQL 2000

-- 2. In SQL 2000, create the table in the database created in step 1
CREATE TABLE [dbo].[ChromValidationValues](
	[Product] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SKU] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LotNumber] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Date] [datetime] NOT NULL,
	[Component] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ConversionFactor] [numeric](18, 3) NOT NULL,
	[TheoreticalValue] [numeric](18, 4) NOT NULL,
 CONSTRAINT [PK_ChromValidatonValues] PRIMARY KEY CLUSTERED 
(
	[SKU] ASC,
	[Date] ASC,
	[Component] ASC
)
) ON [PRIMARY]

GO

-- 3. In SQL 2005, link the SQL 2000 server so we can execute commands on it
--    (cr27972-a is the SQL 2000 server)
USE master;
GO
EXEC sp_addlinkedserver 
   'cr27972-a',
   N'SQL Server'
GO

-- 4. Copy data from the 2005 to the 2000 server (CR27972-A\SQL2005 is the 2005 server)
INSERT INTO [cr27972-a].[Invitrogen_Validation].[dbo].[ChromValidationValues]
SELECT * FROM [CR27972-A\SQL2005].[Invitrogen_Validation].[dbo].[ChromValidationValues]