Hi everyone,

I have an SQL Server 2005 Express database as part of a Visual Studio
project. I also have an SQL Server 2005 server.

I'm trying to backup and restore my database to my web server (hosted with
1&1), which seem to only allow SQL 2000 databases?

I attached the SQL 2005 Express database to my SQL Server 2005 instance, and
backed up. I then tried to restore and got an error. The hosting company gave
me the following explanation...

Question is... how to I "downgrade" my database to SQL Server 2000 ?!

musosdev wrote:
Show quoteHide quote1. install sql server 2000 Desktop Edition (free)
2. make new empty database on 2000
3. use import/export wizard to copy all objects from 2005 database to
2000 database
4. backup/restore to web server

hth

Zarko

Thanks Zarko, I'll give that a go.

Just one question ... do you know if Visual Studio has the export function
required for the database?! I tried to install SQL Server 2005 but it won't
attach to the database :)

Cheers



Dan

Show quoteHide quote

Hi Zarko,

Just trying what you suggested...

1) Install MSDE 2000 - ok, installed a seperate instance on my server
2) Connected to it, created blank database
3) Import/Export

This is where I'm coming unstuck.

Basically, when I select export on my database, it gives me the option to
Export it directly to the 2000 database. This is what I tried to do, and got
the following errors...

----------------------------
Pre-execute (Error)
Messages
Error 0xc0202009: {381AC2E8-30F7-4A71-8E8D-B974C92BF501}: An OLE DB error
has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client" 
Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Native Client" 
Hresult: 0x80004005  Description: "TCP Provider: The specified network name
is no longer available.
".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the
connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 2 - Contacts" (97)
failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)
--------------------

What does this mean? Why is it using TCP, it's on the same server as
SQL2005/ Management Studio.

What about alternative methods of exporting, what would you suggest? Flat
file looks dodgy, I doubt that will pull all the data correct, would you
recommend another Export option?

Thanks for the help :)


Dan


Show quoteHide quote

Hi ,

I am having exact similar problem as you have. Plz tell if you got any luck
doing this ?

Thank you,
Sunny.

Show quoteHide quote

Hi Dan,
Sorry for delaying the response since I took a sick leave yesterday.

It is true that a SQL Server 2005 backup could not be restored to SQL
Server 2000 instance due to structure incompatibility; however you can
directly use Import/Export Data wizard in SQL Server 2005 Management Studio
(SSMS):
Right click the database in SSMS, select Tasks->Export Data...; select the
current database as your data source, click Next; in the window "Choose a
Destination", select the destination server and database, you can create a
new database on the destination server, click Next, select Copy data from
one or more tables or views, click Next, select the tables and views, click
Next and then you can finish the data transfer process.

Generally for restoring a SQL Server 2005 database to SQL Server 2000,
there is no easy way. Data import/export will import the basic schemas and
data into the table, however for the schema constraints such as primary
key, foreign key etc will be lost and you need to manually recreate them
after that. SQL Server 2005 Integration Service can also help transfer data
from SQL Server 2005 to SQL Server 2000, but there are also its limitations.

Also I would like to know:
Why do not you use SQL Server 2005 as your web application's database?  Are
there any known limitations for it?

Please feel free to let me know if you have any other questions or
concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Recently I found this not sure whether this works or not

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000



As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work…
let say satisfactorily. I first didn’t knew that and after a while from the
upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to
crash three or four times per DAY!!!



At first I thought I was being attacked, but soon I realized it was nothing
like that. I then decided to downgrade to an SQL Server 2000 edition. Though
I looked around the internet to find some information on how to do that, I
got very disappointed when I realized that no actual documentation of any
kind could be found for that. So I am posting this thread to inform you on
the procedures I had to follow for this action.



Before beginning I must assume, firstly that the user, who will attempt such
thing, has  a basic knowledge of SQL Environment, secondly that he has the
two versions already installed (both 2000 and 2005), that a basic backup of
the databases has been created and finally that all the 2005 SQL Server Users
have been created at the SQL Server 2000 environment as well.



Step 1 Generating Scripts for the Database Elements and Structures



1)      Right-click over the desired Database at 2005, Choose Tasks and the
Generate Scripts (Option).

2)      At the pop-up Dialog Box click at the Script All Objects in the
selected Databases check box, to activate it and then Click the Next Button.

3)      Set the following Elements to the following Values

a.       Script Collation , set to TRUE

b.      Script Database Create, set to TRUE

c.       Script of SQL Version, set to SQL SERVER 2000

d.      Script foreign keys, set to FALSE

e.       Script Triggers, set to FALSE

Then Hit the Next button

4)      Select the way the generated scripts should be saved (There are
different selections. The most common one is Clipboard). Finally click the
Next button till you reach the end.

5)      Click Finish



After completing this procedure, we have to move to the SQL SERVER 2000
environment. Here, by using the Query Analyzer, we will have to run the
scripts that were generated using the master database. Copy and Paste the
script at the Query Analyzer and run it. After that the Structure of the
Database will be created.



Be careful, the SQL Server 2005 Edition inserts the Views in a random place
through the script. Therefore, all the scripts that are referred to the Views
MUST be moved to the end of the script. If the Query Analyzer shows some
errors do not be bothered. Delete all the elements created from the script
and after you fix the code run it again.





Step2 Moving the data from 2005 to 2000



1)      After completing the previous step successfully, moving the data
follows. Right-click at the 2005 database you used to run the previous step
and select Tasks and then choose the Export Data (option).

2)      From the pop-up Dialog Box, select the Source Db and Click at the
Next Button.

3)      At the next step you will have to choose the destination server and
the destination Database for the Data to be exported. Then Click Next.

4)      A List of all the Source Database’s Elements will appear in the
screen. Select one by one all the Elements you wish to move and for each one
click at the button Edit Mappings (Located at the bottom right corner of the
Dialog Box just under the Elements list). A new Dialog box will pop-up.
Select the Delete rows in Destination Tables option and activate the Enable
Identity Insert Option. (Remember to repeat this action for each of the
selected Element from the list that will be moved.



CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure
why, after multiple tries I have observed that when I tried to move more than
twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to
disable the Enable Identity Insert Option that was activated over the Edit
Mappings Dialog Box. But if the number of the selected Elements is smaller
than 12 no problem seemed to appear.



Step 3 Generating Scripts for the Database Foreign Keys and Triggers



Finally, to successfully finish the downgrade of the Database, the Triggers
and the Foreign Keys of the DB must be produced. The procedure that should be
followed is the one stated next:



1)      Right-Click at the SQL 2005 Database and Select from Tasks Menu the
Generate Scripts Option.

2)      Using the pop-up Dialog Box make sure that the check box Script All
Objects in the selected Databases is not enabled and hit the Next Button.

3)      Set all the Elements on the List to a False Value except the ones
that follow:

a.       Include IF NOT EXISTS , set to TRUE

b.      Script Owner, set to TRUE

c.       Script of SQL Version, set to SQL SERVER 2000

d.      Script foreign keys, set to TRUE

e.       Script Triggers, set to TRUE

Then Hit the Next button

4)      After finishing reading the Elements of the Database, a new list
will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of
the Database and hit the Next Button.

5)      At the screen that follows hit the Select All button and the Next.

6)      Select the way the generated scripts should be saved (There are
different selections. The most common one is Clipboard). Finally click the
Next button till you reach the end.

7)      Click Finish Button.



After completing this procedure, we have to move to the SQL SERVER 2000
environment. Here, by using the Query Analyzer, we will have to run the
scripts that were generated using the master database. Copy and Paste the
script at the Query Analyzer and run it. After that the Foreign Keys and the
Triggers of the Database will be created.



After these steps the database should be fully functional under the SQL
Server 2000 edition.

Plz let me know if this has done trick for you

Sunny.

Hi Charles,

Thank you for your response.

Let me start with the last question first :)

I'd be absolutely delighted to use 2005 as the database on our website.
Unfortunately, our hosts (1&1 Internet here in the UK) do not support it.
Their databases are all SQL Server 2000. Quite annoying considering 2008 is
about to drop.

Anyway, you mention the following method...

This is exactly what I tried, when I got the error...

----------------------------
Pre-execute (Error)
Messages
Error 0xc0202009: {381AC2E8-30F7-4A71-8E8D-B974C92BF501}: An OLE DB error
has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client" 
Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Native Client" 
Hresult: 0x80004005  Description: "TCP Provider: The specified network name
is no longer available.
".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the
connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 2 - Contacts" (97)
failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)
--------------------

I'm not fussed about Primary Keys, etc - I'm not using any. There are a few
identity fields in there though.

Could you explain what the error above means, and why it's happening. Is
there some other conversion problem happening that SQL is hiding from me?!
Where can I find out?

Thanks,


Dan


Show quoteHide quote

Restoring a column in a table
Live server running out of space - Can't reduce Transaction Log!
database and tlog backups - MSSQL 2000 & MSSQL 2005
Copy views from one database server to another
Optimizing and shrinking large highly-transactional database