I started a project using SQL 2005 and now the customer has decided they can't go to 2005 yet so I need to convert it back to 2000. I didn't use any new features of 2005. How do I move it back to SQL 2000?Hi Steve,
Well, backup of 2005 doesn't restore on SQL 2000. However, you can generate SQL Script from objtecs and run into SQL Server 2000 Database.
Follow this steps:
1. Create new database in SQL 2000
2. Genarete script from objtects on the SQL 2005
3. Execute this script on the SQL 2000
If you have data, use bcp utilities to export and import.
Nilton Pinheiro|||
I am have problems with the syntax on the sql05 scripts. They are not running in 2000.
Has anyone else had problems?
|||socalmp, can you give some examples? Hard to say without them.|||
I have a db I imported into sql05 and now I need to transfer that db to a hosting company using sql2000.
I go right click on db >Tasks > generate scripts > and a wizard pops up to walk thru the settings for the script. I turn everything to false except script foreign keys, primary keys and unique keys because I have some relationships in the tables. The script version is sql2000 and the behavior is to generate create statements only. this is what I get:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [patients](
[patientID] [int] IDENTITY(1,1) NOT NULL,
[last] [text] NULL,
[first] [text] NULL,
[dob] [datetime] NULL,
[gender] [text] NULL,
[sexuality] [text] NULL,
[ethnicity] [text] NULL,
[job] [varchar](50) NULL,
[company] [varchar](50) NULL,
[homephone] [varchar](50) NULL,
[homephone2] [varchar](50) NULL,
[mobile] [varchar](50) NULL,
[businessphone] [varchar](50) NULL,
[streetadd] [varchar](50) NULL,
[state] [text] NULL,
[zip] [nvarchar](50) NULL,
[country] [varchar](50) NULL,
[email] [varchar](50) NULL,
[email2] [varchar](50) NULL,
[webpage] [varchar](50) NULL,
[im] [varchar](50) NULL,
[reffered] [varchar](50) NULL,
[type] [varchar](50) NULL,
CONSTRAINT [PK_patients] PRIMARY KEY CLUSTERED
(
[patientID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [treatment](
[patientID] [int] NOT NULL,
[treatmentdate] [datetime] NULL,
[product] [varchar](50) NULL,
[amount] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [notes](
[patientID] [int] NOT NULL,
[notes] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[treatment] WITH CHECK ADD CONSTRAINT [FK_treatment_patients] FOREIGN KEY([patientID])
REFERENCES [patients] ([patientID])
GO
ALTER TABLE [dbo].[notes] WITH CHECK ADD CONSTRAINT [FK_notes_patients] FOREIGN KEY([patientID])
REFERENCES [patients] ([patientI
When You run this in query analyzer I get this error:
Line 29: Incorrect syntax near '('.
And that is from this line:
CONSTRAINT [PK_patients] PRIMARY KEY CLUSTERED
(
I have tried many times with no success to get the script wizard to make a script compatible with SQL2000. My t-sql skills need improvement obviously, but I don't know why this has to be so difficult. Enterprise manager seemed easier to work with then the new sql05. I am sure I just need to get up to speed with its features
Any suggestions? I am trying to migrate a db from sql05 to sql 2000 using Query Analyser or csv import wizard only. I was able to previoulsy generate scripts from enterpirse manager to create the db with Query Analyser.
|||
Here's a script from Sql2000 made with enterprise manager and it ran just fine in the query anaylzer:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_notes_patients]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[notes] DROP CONSTRAINT FK_notes_patients
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_treatment_patients]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[treatment] DROP CONSTRAINT FK_treatment_patients
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[notes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[notes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[patients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[patients]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[treatment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[treatment]
GO
CREATE TABLE [dbo].[notes] (
[patientID] [int] NOT NULL ,
[notes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[patients] (
[patientID] [int] IDENTITY (1, 1) NOT NULL ,
[last] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[first] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dob] [datetime] NULL ,
[gender] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sexuality] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ethnicity] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[job] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[homephone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[homephone2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mobile] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[businessphone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[streetadd] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[webpage] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[im] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reffered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[treatment] (
[patientID] [int] NOT NULL ,
[treatmentdate] [datetime] NULL ,
[product] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[amount] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[patients] WITH NOCHECK ADD
CONSTRAINT [PK_patients] PRIMARY KEY CLUSTERED
(
[patientID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[notes] ADD
CONSTRAINT [FK_notes_patients] FOREIGN KEY
(
[patientID]
) REFERENCES [dbo].[patients] (
[patientID]
)
GO
ALTER TABLE [dbo].[treatment] ADD
CONSTRAINT [FK_treatment_patients] FOREIGN KEY
(
[patientID]
) REFERENCES [dbo].[patients] (
[patientID]
)
GO
So I am not sure What the difference is betwen the scripts?
|||
Maybe your default collation is not set to SQL_Latin1_General_CP1_CI_AS
I noticed in the 2000 script it is being specified all the way down. That might need to be specified in your 2000 instance. Try adding the collation statement to your columns in the 2005 script and see.
|||There is an easy way to solve the problem.
Open the Microsoft SQL Management Studio, then in "Object Explorer" right click on your Database->Tasks->Generate Scripts
In the Wizard
Script Window Click Next, Select You DB, Click Next and in the Choose
Script Option Step, Change the value of "Script for Server Version"
from "SQL Server 2005" to "SQL Server 2000" and go on.
When you click "Finish" button, your script will SQL 2000 compatible.
Good luck.
|||I tried this and the first script is a product of SQL 2005 and their acript generator. I made sure it was SQL 2000 compatible and it will not run in a SQL 2000 query analyzer. Strange!|||
On the script side, I looked more closely and I think the (IGNORE_DUP_KEY = OFF) syntax is incorrect. I believe you either have IGNORE_DUP_KEY specified or it is absent (which is the equivalent of OFF).
Another way you could accomplish your goal is to set your database to a 2000 version format, make a backup, and send it to your hosting provider to restore on their server. That solution should at least solve any script problems.
Run the following command against your database in SQL Management Studio and then create your backup.
sp_dbcmptlevel 'MyDatabaseName', 80
Hope that helps.
Hugh
|||
While generating the Script
We have to select option
Script for Server version as SQL Server 2000
|||
Created the databse with compatibility level set to sql200.
restored my client database from sql2005
i run the command "sp_dbcmptlevel 'MyDatabaseName', 80"
backup the database
everyrhing was ok.
I go to the enterprise manager of my sql2000 and restore the backed up database and this is the error i got.
"Too many backup devices specified for backup or restore; only 64 are allowed. Restore Database is terminating abnormally."
|||Has anybody found useful input on the 64 backup device limit? Same problem here moving a SQL 2005 db to SQL 2005 or 2000.|||
Hi Byrnie,
In my case I have SQL2005 running on my laptop. I needed to move the database onto my server which is currently running SQL 2000. On the laptop and in SQL Server Management Studio I selected the import/export Wizard. Using the wizard I was able to export my SQL2005 tables across to the SQL2000 server. I have not yet completed the process and have just started the job. I will have to verify the data as soon as the job has completed. This is the first time I am contributing anything to a forum. I hope it helps
|||Hi frnds,
I have done the process of Converting SQL 2005 Database into SQL 2000 Database.
1.Create Database in SQL Server 2000 which is the name into SQL 2005.
2.From SQL 2005 Mgmt Console Studio , from Database - tasks - Generate Script
-then select Database -Script for Server Version - Change it from SQL Server 2005 to SQL Server 2000
3. Do the process as per wizard.
4.We can run that Script into SQL Query Analyzer 2000 for that selected database where you want to move it.
Its working fine.I got whole database table creation and whole relationship which was made into SQL Server 2005.
Gr8t.. thanks for your help.
