How do I create a SQL 2005 database from a SQL 2000

I have a SQL 2000 database on one server that I'm trying to transfer to a new server.  The new server has Small Business Server R2 Premium Edition, which includes SQL 2005.

I tried to transfer the database in the normal way that I'd create a copy database, which was: -
1.   Backup the source database
2.   Create a new database (empty) in the new server
3.   Restore to this database from the backup taken in #1.

Of course on previous occasions I wasn't changing version, but I expected that this would either fail immediately (message "incorrect version" or some such), or else work.

At first this appeared to transfer OK.  It took about 20 minutes to create the backup (just over 5GB), and about the same time to restore it.  The restore terminated with a message "Database has been restored successfully").    However, the database's icon in Management Studio's Object explorer showed a little up arrow, and alongside the database name "(restoring ....)". Clicking on the database icon did not expand it.  3 hours later it still was like this.   Examining the server showed zero activity.

I expect that it's failed, and I should delete the new "(restoring...)" database and start again.  How should I create this SQL 2005 database?


8/23/2007 9:58:59 AM 29906 articles. 0 followers. Follow

3 Replies

Similar Articles

[PageSpeed] 47

You're going about the upgrade in the right way (backup and restore) but there are some prerequisites for this to work. For a start the SQL 2000 must be patched with at least service pack 3 (download and patch if necessary).

I also suggest that you don't restore into a new database - that you create the "empty" database on SQL Server 2005 and restore the 2000 backup file into that. This eliminates a ton of other problems I've seen - which don't match your own problem but may also sort you out. I'd be interested to know!

Nearly forget, when restoring into an existing database don't forget to go into "Options" and check "Overwrite the existing database" option - that catches people out. Good luck.

Articles & Training for SQL Server and ASP.Net 2.0

Check out our tutorials for going live on GoDaddy
8/23/2007 5:55:38 PM

There are a few things you would need to do after you have successfully restored the DB onto 2005.

(1) Change the comaptibility level of the DB to 90. It would have been 80 in 2000.

(2) Reindex ALL the tables followed by an UPDATE STATISTICS WITH FULLSCAN on ALL tables.

(3) You might need to check the syntax of the procs - if you have any index hints (or others) you would have to prefix with WITH keyword. Parse the procs and you will see the errors.

Dinakar Nethi
Life is short. Enjoy it.
8/23/2007 6:40:52 PM

Thank you.  I cleared the logs and did it all again, using a backup that was NOT an incremental backup (duh!) and remembering the "override the database" optin, and it all transferred cleanly and the database came up without problem.  

Regards, Robert

8/24/2007 7:42:53 AM

Similar Artilces:

How to create SQL Mobile 2005 database from a MS SQL 2005 database?
Hi guys,I'm trying to find solutions to help me in creating a SQL Mobile 2005 database from a SQL 2005 database, with the same table structure and data.May I know how can I achieve this?Thanks.Regards,Jenson Hi Jenson, Visual Studio 2005 or SQL Server 2005 is able to open SQL Mobile 2005 database. However you need either SQL or .NET code to convert SQL Mobile 2005 database this way. Here is an example from MSDN forums which is to convert to xls instead: Ji MaSincerely,Microsoft Online Community Suppo...

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...

How to create a data diagram in SQL Server 2005 Management Studio for a SQL Server 2000 database
Hello: I have an old database created in SQL Server 2000, now I can open, access it in SQL Server 2005 Management Studio, but I can't create Data Diagrams. Please advise on how to make it work.... Thank you Expand the database folder in Object Explorer and the first folder under the databases is the "Database Diagrams". Right click -> New database diagram.***********************Dinakar NethiLife is short. Enjoy it.*********************** Thank you for replying :)  but that only works for database created in SQL Server 2005, my database is created in SQL Server ...

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?.....   this article explains it.,289625,sid87_gci1149585_tax301536,00.html hth,mcm 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 cr...

sql 2000 to sql 2005
i have developed my project in 2.0 +ajax +c# as backend with sql server 2000 as back end now my question is can i change my sql server to 2005 will it gives any problem in furture all my queries will run properlyMark As Answer If my reply helped you. syedwna:now my question is can i change my sql server to 2005 There should be no problems as long as you migrate all the data, procedures, functions and all required data from SQL2000 to SQL2005. ...

Migrating VS2003 datasource from SQL 2000 to SQL 2005
Is it necessary to convert my VS2003 project to VS2005 before converting the datasource from SQL 2000 to SQL 2005? Or can I just change the datasource without converting the project? Hi delossan, For the first question, the answer is no. Both SQL Server versions can work with Visual Studio 2003 and Visual Studio 2005. For the second question, the answer is yes. Visual Studio 2003 can work with SQL Server 2005.    Sincerely,Benson YuMicrosoft Online Community Support Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can ...

Move whole database from sql 2000 to sql 2005
Hi everyone,     I am trying to move a database from sql 2000 to sql 2005 by Integration services. and i am able to move only data i.e tables and data in tables to sql 2005   but i couldn't move stored procedures and keys of the table (neither primary nor foreign keys) i am not sure what is the best way to be move these whether should i move them by scripts or is there a way to move them through Integration services or is there any other way to move them  if any one could please advice me i would be grateful  Thanks.      you...

How to restore SQL 2000 database in sql server 2005
Hi,I have a sql server 2000 database and I want to restore it in sql server 2005. Will it be possible? If yes please let me know the steps to follow.When I tried it my one point you need to specify force to restore...then onwards the system CRASHES (HANGS).I tried several times but not happy.Please suggest me the steps in detailThanks  Hi, Please check this link: Hope this helps you. Thank you Hi, Restoring SQL 2000 on SQL 2005 should not cause any problems. I do so regularly. It is no different to restoring a SQL ...

I cannot stop Visual Studio.NET 2005 from looking for SQL Server 2005 instead of SQL 2000
I would like to know how to set SQL2000 as the default database server when running any webapplication. I have installed Visual Studio.NET and SQL Server 2000 locally in my laptop.  But anytime I want to run a webapplication I am getting the following error:System.Data.SqlClient.SqlException was unhandled by user code  Class=20  ErrorCode=-2146232060  LineNumber=0  Message="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 doe...

SQL Attached mdf database files VS database imported into or created in SQL Server 2005
 Hi all (newbie @ @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases. AFAIK it is only for deployment conveniance, I have always imported the database directly into SQL Server and set up the connection string accordingly.Don't forget to click "Mark as Answer" on the...

Transfer database created in SQL Express to SQL Server 2005
Using VWD 05 Express & SQL Server 05 Express, how do I transfer my database to my host that is using SQL Server 2005. On the SQL Server express web site one of the features listed for SQL Server 05 Express is: Transfer any database created in SQL Express to other editions of SQL Server 2005. Thanks You can just use a simple export dump of the schema and data.  Im sure with the SQL Server Management Express you can do this.Eric RamseurRainbow Portal 2.0 AdminDownload Rainbow 2.0!!Rainbow Code Rainbow Portal CommunityC# 200...

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...

Move a Table from one SQL 2005 Database to Another SQL 2005 Database
I created a table in an SQL 2005 database, however, I want to move that table and the data to another SQL 2005 database (within the same server). I am new to SQL 2005 and detailed instructions would be greatly appreciated. Thanks Bill You have two options the first is clean but the second is easier.  Try the links below for details.  Hope this helps.  Kind regards,Gift Peddie Thanks, but the documentations is somewhat overwhelming and was hoping of a ba...

Convert sql server 2000 database to sql express 2005
Hi; I have a database in sql server 2000 and i want to convert it to sql express 2005, so please tell me how I can do this. Thanks Adnan I havent worked with 2005 Express but couldnt you back up the 2000 copy and restore in 2005?***********************Dinakar NethiLife is short. Enjoy it.*********************** Hi adnan152,The first question I want to know is that do you have SQL Management Studio Express installed on your machine?  Well, if not you can download it by clicking this link:

Web resources about - How do I create a SQL 2005 database from a SQL 2000 -

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 ...

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 ...

Five million customers affected by Vtech database hack
Toy and educational material seller Vtech has revealed five million customers were affected by a hack of its app database and has also suspended ...

Ben Carson calls for database of all foreigners in US
Boston Herald Ben Carson calls for database of all foreigners in US Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

GOP rivals condemn Donald Trump for backing Muslim database
COLUMBIA, S.C. (AP) — Retired neurosurgeon Ben Carson said Saturday that he wants to expand the government's surveillance operations aimed at ...

Donald Trump wants "surveillance of certain mosques," database of refugees - Videos - CBS News
... rally in Birmingham, Alabama, Republican presidential candidate Donald Trump called for increased scrutiny over mosques, along with a database ...

Trump Wants A Database For All Syrian Refugees Who Enter America
Trump Wants A Database For All Syrian Refugees Who Enter America

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

VTech Admits Lack of Database Security Opened Door to Hack
A SQL injection, a common software flaw, was found to be the root cause in the VTech breach.

Animal Venom Database Could Be Boon To Drug Development
A new public database that catalogs animal toxins could spur the development of new drugs made with compounds found in animal venom.

Resources last updated: 12/5/2015 8:52:49 PM