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.

Check out this:

How to: Upgrade to SQL Server 2005 with the Copy Database Wizard

|||

The article you mentioned does not apply to the 2005 Express Edition. The Management Studio for the Express Edition contains no Copy database option that the above article mentions.

I think there is NO tool for doing this upgrade for Express Edition of SQL: Server 2005.

|||

Hi,

Just want to create a copy of SQL Server 2000 database in 2005 ? Why don't you backup you database in SQL Server 2000 and restore it in SQL Server 2005.

Thanks.

|||

Hi,

As mentioned above you can backup ur database and then restore it in SQL 2005.

PLease have a look at this article which explains the steps for backup and restore:

http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

Please let me know if you have any queries.

How to cosolidate databases of SQL Express from multiple installations?

1. install a SQL Express instance such as myexpress;

2. create some databases with tables such as db1 and db2;

3. Reinstall SQL Express with the same instance name "myexpress". I assume we need reinstall SQL Express myexpress again becuase of bad operations.

4. Open the myexpress by management tool but I can not see db1 and db2 databases but the datafiles and logfiles are in the data directory.

The question is what I need to do so I can consolidate exited databases so they can be seen in "myexpress" instance by management tool?

hi,

WhyHere wrote:

1. install a SQL Express instance such as myexpress;

2. create some databases with tables such as db1 and db2;

3. Reinstall SQL Express with the same instance name "myexpress". I assume we need reinstall SQL Express myexpress again becuase of bad operations.

4. Open the myexpress by management tool but I can not see db1 and db2 databases but the datafiles and logfiles are in the data directory.

The question is what I need to do so I can consolidate exited databases so they can be seen in "myexpress" instance by management tool?

reinstalling the database engine will actually overwrite your used master database... the master database contains the "registration" of each user database so that the "clean" master does not include those registrations... you can re-attach your existing database files via the

CREATE DATABASE xxx

ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xxx.mdf'

)

FOR ATTACH ;

statement... please have a look at http://msdn2.microsoft.com/en-us/library/ms176061.aspx for further info about it's synopsis... this will result re-registering a database from it's physical files...

BTW, overwriting the master database will also clean-up all logins information about all SQL Server and Windows NT principals.. associated (for SQL Server standard logins only) database users must also be cleaned/syncronized, for each user, via the sp_change_users_login system stored procedure, as reported in http://msdn2.microsoft.com/en-us/library/ms174378.aspx..

regards

How to copy tables to another database?

I have two database instances on the sql server 2005. I'm using MS SQL Server Management Studio. How do I copy tables from db1 to db2 that is on the same sql server?

Using Management Studio (SSMS), in the Object Explorer pane, right click on the database name and select [Tasks], then [Export Data].

Follow the prompts to copy tables from one database to another.

|||

Many thanks for the help. I was able to copy the tables of one database to another database on the same server. The next question is, how do I do the same thing but copying to a database that is on a different sql server? I tried it and I got this error:

TITLE: SQL Server Import and Export Wizard

The operation could not be completed.

ADDITIONAL INFORMATION:

Login timeout expired
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.
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. (Microsoft SQL Native Client)

BUTTONS:

OK

|||

(I suspect that you are attempting to upload to a Host provider's server.)

However, even if that is an incorrect assumption, it appears that you are not able to connect. Below are some resources for solving connection issues.

Also, you 'may' find that the Database Publishing Wizard is useful. The DPW is designed to create a script file from tables and data, and then that script file can be transferred to the Host using FTP, and executed on the Host sever. I've included a link to the DPW.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

Configuration - Guideline for Connectivity Question Posting
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

|||Arnie, thank you so much for your help. I got it working now with the DPW.