Upgrade SQL 2000 database to SQL Express?

Upgrade SQL 2000 database to SQL Express?

• amienel

  I initially installed 2005 Express on my game computer and then created all the tables, constraints, keys, stored procedures, etc. Then I wrote a VB6 utility program to copy records from SQLServer2000 to SQLServer2005 Express. It was quick!!!

  I checked all the tables to make sure the keys where there and correct. I copied over about 500 meg of data. Not much is it !

  Then I blew away my development machine and installed WindowsXP (sp2), .Net v2.0, Dx9.0c, etc. Installed 2005 Express and then unattached the databases on my gaming rig and then attached them to the new development computer. Was easy!!!

  Then I uninstalled 2005 Express from my gaming computer. Can't have this interfere with my gaming. lolol

  
  

• itmatters

  I converted from 2000 to 2005 Express and lost some performance.

  Please post your performance thoughts after you have used 2005 Express for a while.
  I had also switch from W2K to WXP.

  
  

• hafe

  I have done this several times and the upgrade worked with out a problem. What you will have to do though is to grab one of the management tools or learn to use the command line tools. Once the tools are under stood the upgrade process is as simple as backing up the database and restoring it to a new database on the SQL Express System.

  What I did was to use the CTP release of the management studio, create a database using the tools. Then backup the SQL2000 database using the Enterprise manager application, and restore it to the SQLEE system. Then as I was going to use the database as a User Instance (Separate file attached to the database engine at run time using the configuration string) I detached the database using the tools and then moved the mdf file to the new applications location. Note that you may need to play with the security for the new database.

  Microsoft SQL Server Management Studio Express - Community Technology Preview (CTP) November 2005
  http://www.microsoft.com/downloads/details.aspx FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en

  SQL Server Express Utility
  http://www.microsoft.com/downloads/details.aspx familyid=FA87E828-173F-472E-A85C-27ED01CF6B02&displaylang=en

  SSEUtil.exe is a tool that lets you easily interact with SQL Server. Among other things, it allows you to:
  ? Connect to the main instance or user-instance of SQL Server.
  ? Create, attach, detach, and list databases on the server.
  ? Upgrade database files to match the version of the server.
  ? Execute SQL statements via the console (similar to SQLCMD).
  ? Retrieve the version of SQL Server running.
  ? Enable and disable trace flags (for example, to trace SQL statements sent to the server by any client application)
  ? List the instances of SQL Server on the local machine or on remote machines.
  ? Checkpoint and shrink a database
  ? Measure the performance of executing specific queries.
  ? Create and playback lists of SQL commands for the server to execute.
  ? Log all input and output.
  

  
  
  

• asiu

  When you converted across did you do any maintenance on the database, I found that if I did a rebuild of the indexes and did some general clean up the system performed the same or even quicker.

  
  
  
• Upgrade SQL 2000 database to SQL Express?