moving an sql server 2005 database to a sql server 2000 database

I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database.

I'm not sure the best way to do this.......

Can anyone enlighten me?.....

 

0
pizzamaker74
12/12/2006 7:19:47 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
1074 Views

Similar Articles

[PageSpeed] 4

pizzamaker74:

I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database.

I'm not sure the best way to do this.......

Can anyone enlighten me?.....

If both are in the same network or box just register the 2005 with the 2000 create a blank database in 2000 and use DTS to copy everything to the 2000.  You may still need to move permissions that is covered in the FAQ below.  Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190631.aspx

http://forums.asp.net/thread/1454694.aspx

 


Kind regards,
Gift Peddie
0
Caddre
12/12/2006 4:43:35 PM

Caddre;

Thanks heaps.

This document is also very useful: 

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
pizzamaker74
12/15/2006 3:35:22 AM
I am glad I could help.
Kind regards,
Gift Peddie
0
Caddre
12/15/2006 5:15:57 AM
Reply:

Similar Artilces:

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

Moving SQL Server 2005 Database to SQL Server 2005 Express
Hi, I have SQL Server 2005 in my Development machine but the hosting server has only SQL server 2005 Express. I was wondering if I develop the DB in SQL server 2005 would be able to move/deploy it to SQL Server Express in the production server? If so how? Can I just generate the script and then run it on the productions server? Or there is a better/easier way? Thanks in advanceShuaib----------------------- I don't anticipate any problems creating database in SQL Server 2005 and moving it to Express. If you need to generate the script to create an empty shell (no d...

Restoring a SQL Server 2005 database into SQL Server 2000
Hey guys  We currenlty have SQL Server 2000 installed inhouse for testing.  A client has given us a backup of their 2005 database and i'm wondering if the restore will work into 2000, or any issues i should be aware of. Should i just install msde locally on my machine and restore the database?  thanks Hi,Ask your client to backup database as 2000 format. There is an option for this in Microsoft SQL Server Managment Studio. Then download MSSMSE and install for yourself and try to restore database from it.If database doesn't use any 2005 features (rowcount for exampl...

How migrate SQL server 2000 database to SQL Server 2005 Express
I  have a  SQL server 2000 database.I have to migrate it to SQL Server 2005 Express.But I don't know how to do. Is any one can help me ? Thanks You can detach the database using sp_detach_db and then re-attach it to your SSE instance using sp_attach_db.  This should work seamlessly, but back up first, just in case.Adam MachanicSQL Server MVP...

Can Sql Server Express 2005 Run a Sql Server 2000 database?
Should be a quick question:I've got a client with a Sql Server 2000 database.  He wants to hook it up to a server that is only running Sql Server Express 2005.  Can this work? Thanks!-Craig U-Sports.net - Taking Fantasy Football to School Tell him to Backup the database and send you the .bak you can restore it as 2005 database.  I have posted a FAQ about moving SQL Server databases and there is a free chapter about Backup and Restore in the link below.  Hope this helps.  http://forums.asp.net/thread/1454694.aspxKind regards,Gift Peddie Thanks!  I will rec...

Cannot restore database in the computer is setuped SQL Server 2000 and SQL server 2005
Hi All I have Computer is setuped SQL Server 2000 and SQL server 2005 when I restore SQL Server 2005 it's OK but when it's not OK when I restore in SQL Server 2000. The Error as follows: Please help me to solve this problem. Thanks   check this url, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169145&SiteID=1 http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=42121&enterthread=y...

SQL Server 2005 error when trying to connect to sql server 2000 database
Hi all.I am working on asp.net 2.0 with back end as sqlserver 2000. but when trying to connect to the database i am getting the following error displayed An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Description: An unhandled exception occurred during the execution of the current web request. Please review the...

How to create a copy of SQL Server 2000 database in SQL Server 2005 Express?
I have a database called 'DB1' in SQL Server 2000. I want to create the same database in SQL Server 2005 Express including the original data in tables. How would I do that? I cannot find any option to do this upgrade in SQL Server Management Studio.sun21170 Check out this:How to: Upgrade to SQL Server 2005 with the Copy Database Wizard Darrell Norton, MVPDarrell Norton's BlogPlease mark this post as answered if it helped you! The article you mentioned does not apply to the 2005 Express Edition. The Management Studio for the Express Edition contains no Copy database opti...

Can microsoft SQL Server 2005 database upgrade to microsoft SQL Server 2008 database?
I have a microsoft SQL Server 2005 database, I don't know how to upgrade the 2005 database to 2008 database, I try to use SQL Server Management Studio Express 2008 to open the 2005 database, it's OK, does it mean that the 2005 database have been upgraded to 2008 database? BTW, is SQL Server 2005 database compatible with SQL Server 2008 database completely?SuperCool Multiple ZIP - A utility to unzip multiple files and work with multiple zip filesSuperCool Random Number Generator Yes. It is compatible. Anyhow go through this url for more informationhttp://msdn.microsoft.com/en-us/li...

need help
Hi there,     I am not sure if this is the right place to seek help for the problem i have but as i don't see any other link to discuss the situation i have i am just posting it here.  To explain a little bit about the project i am on..... Originally the  appication(developed in asp.net and vb) i am assigned to was developed by a different team and most of the databases  were on  SQL server 6.5 servers. So they have used oledb connections where ever they had to connect SQL server 6.5 data sources. Lately the client has moved a...

Problem with showing diagrams when attaching a Sql Server 2000 database in Sql Server 2005
I have a database created in MS SQL Server 2000 with some tables and diagrams.I have attached that database in SQL Server 2005 but when i want to access and showthe diagrams i face the following error message :Database diagram support object can not be installed because this database does not havevalid owner,to continue first use the files page of database dialog box or alter the authorizationstatement to set the database owner to valid login then add the database diagram support object.I have created a role with required permissions but i still have the problem(I still can't show the di...

Can I use Bugzilla with Sql server 2000 or sql server 2005 as backend Database
I have Just Successfully installed Bugzilla My installation are Bugzilla 2.2 Apachec Webserver ActivePerl 7.8 Mysql 5.0 Now I like That My Database should be Sql server 2000 or sql server 2005 Can I do that [As I also see that I required some Perl module of Mysql as well] ...

Move from SQL Server 2000 to SQL Server 2005 with VS 2005
 We have a new database server for SQL Server 2005.  To use our old VS 2005 websites, which were connected toanother database server with SQL Server 2000, what should I do?  Just change the server name of the connection stringin the Source code and/or Web.config?  My preliminary testing seemed to be not effective.TIA,Jeffrey wonjartran:  We have a new database server for SQL Server 2005.  To use our old VS 2005 websites, which were connected toanother database server with SQL Server 2000, what should I do?  Just change the server name of the connectio...

SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well.  (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed.  I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...

Web resources about - moving an sql server 2005 database to a sql server 2000 database - asp.net.sql-datasource


Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Social Side of Code, Database CI and REST API Testing in Methods & Tools Winter 2015 issue
... for software developers, testers and project managers – has published its Winter 2015 issue that discusses the social side of code, database ...

A look ahead at the state of the database in 2016
... identity management, anti-phishing education, and so forth. Ultimately, hackers want access to the contents of an enterprise's database. Security ...

Huge database of 191 million U.S. voters found on the open Internet
... such bulk information online, a recent discovery seems to indicate. According to VentureBeat , a security researcher found the exposed database ...

Top US School District Switches to Rimini Street for Oracle Database and Oracle E-Business Suite Support ...
Douglas County School District using savings from Rimini Street independent support to develop district-wide data visualization capabilities ...

IDG Contributor Network: Shrinking an already ginormous SharePoint database transaction log
Last month, I wrote about how to avoid having ginormous transaction logs within SharePoint databases. But what if it’s too late, and your files ...

Massive Voter Database Left Open For Public Consumption
Gosh, we could have avoided all the angst about the Clinton/Sanders voter database breach if only we'd known about this major database , accessible ...

18 million targeted voter records exposed by database error
A second database containing voter records has been exposed due to a configuration error. While the first held records that some dismissed inconsequential ...

Former Ohio cop uses cop database to stalk victim
... stalked her like an average person would, but stalked her in the way that only a police officer could: he stalked her utilizing a police database ...

Resources last updated: 1/22/2016 8:07:13 AM