Solved

Switching Back to SQL 2000

Posted on 2007-11-19
17
177 Views
Last Modified: 2010-03-19
Due to some reason we want to switch back from SQL 2005 to SQL 2000.
Can a database of SQL 2005 be attached to SQL 2000.
0
Comment
Question by:Mehram
  • 7
  • 5
  • 2
  • +3
17 Comments
 
LVL 21

Expert Comment

by:jvuz
Comment Utility
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>Can a database of SQL 2005 be attached to SQL 2000.
no, you have to migrate the tables/data back the "hard" way.
0
 

Author Comment

by:Mehram
Comment Utility
Is the above link correct.
0
 
LVL 2

Expert Comment

by:eulac
Comment Utility
no you cannot do that....

if you are in a live environment, you can install a SQL 2000 in a standby server then backup all the databases in SQL 2005 prod server, then reinstall SQL 2005. then revert.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
As Angel said, you need to use 'BCP' or some other data transfer methods to migrate the data from 2005 to 2000
0
 

Author Comment

by:Mehram
Comment Utility
Hi Jvuz
I am unable to browse your link
0
 
LVL 2

Expert Comment

by:eulac
Comment Utility
you can use the built-in import/export utiliy of SQL to trasnfer data from 2005 to 2000.
0
 
LVL 2

Expert Comment

by:eulac
Comment Utility
just a reminder based on my experince as DBA , it is very critical to migrate a data if you dnt have a running standby server.

This is true if you will just rely your backup DB in a file and not in a running SQL server. You may have to ask your self first before backing up in BCP, what if the backuped data in file did not run?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 25

Expert Comment

by:imitchie
Comment Utility
mehram, how long have you migrated, how many sql 2005 features have you used and how much data are we talking about (combined total of all databases' files).  there may be an easier way to do this.
0
 

Author Comment

by:Mehram
Comment Utility
Hi imitchie

2 Databases
approx 80 tables
0
 
LVL 2

Expert Comment

by:eulac
Comment Utility
why dont you just make a full backup then restore the database.

steps:
1. make a full backup in 2005
2. restore the database in 2000

refer to bottom page of this link on how to make a full backup/restore.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
0
 

Author Comment

by:Mehram
Comment Utility
Hi eulac
<<you can use the built-in import/export utiliy of SQL to trasnfer data from 2005 to 2000.>>

In SQL 2000 I cannot register SQL Server 2005 and In SQL 2005 I cannot connect to SQL 2000 Server , then How Can I connect the two database of two differ ent SQL Server in DTS at the time of import/export
0
 

Author Comment

by:Mehram
Comment Utility
<<why dont you just make a full backup then restore the database.>>

I have already tested and have found that full backup of SQL 2005 cannot be restored in SQL 2000
0
 

Author Comment

by:Mehram
Comment Utility
Hi eulac

<<In SQL 2000 I cannot register SQL Server 2005 and In SQL 2005 I cannot connect to SQL 2000 Server , then How Can I connect the two database of two differ ent SQL Server in DTS at the time of import/export>>
No , I am wrong

In SQL 2005 , SQL 2000 can be registered / connected.
Then we can transfer the data through DTS
0
 

Author Comment

by:Mehram
Comment Utility
Hi angel
I am thinking

I will connect SQL 2000 server in Management Studio.
I will then transfer the data through DTS.

Am I right?
0
 
LVL 2

Accepted Solution

by:
eulac earned 350 total points
Comment Utility
here's the detailed step by step procedure that is tested in one of the forum. please note that your SQL 2000 is updated with SP4.

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.


www.hellasinternet.com
Panos Tzirakis & George Dounavis
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 150 total points
Comment Utility
i was after the size in GB terms, to see if it's feasible to do the following:
install sql 2000, install sp4, run the command detailed in the link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
then from sql2005 management studio, you can generate creation of full database schema
run that on 2000, less that constraints that may interfere with inserts
link them up
EXEC sp_addlinkedserver
@server = N'servername', @srvproduct = N'', @provider = N'SQLNCLI',
@datasrc = N'SERVER=servername;Integrated Security=True'
then for each table, do a select .... into ... from 2005->2000
finally, run the bottom part of the script that creates constraints

i find it easier to work this way with both servers online, as i have inhouse tools for generating bulk select... into.. statements.  but if you can link the servers, sql 2005 management studio SSIS for transfering data should work as well
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now