I recently helped move data from a small SQL Sever 2000 database to SQL Server 2008 from one hosting environment to a different hosting environment. I tried several approaches and fortunately found one that worked well. You can find the detail in the post.
Approach #1: SQL Server Import/Export Wizard
People who were familiar with SQL Server 2000 probably enjoyed the Import and Export wizard that came with SQL Server 2000 Enterprise Manager. I used it all the times to upload a local database to a hosted server and vice versa. The wizard did exactly what it was designed for. However, this wizard doesn’t work with SQL Server 2005 or 2008. In order to import/export or copy an entire database, you will need a different tool available to SQL Server 2005/2008.
This tool (DTSWizard.exe) in SQL Server Management Studio allows you to copy data from tables and views only; it doesn’t import/export other objects such as stored procedures. Therefore, this option didn’t work for what I wanted to do.
One quick note about the wizard. By default, the tool doesn’t create identity columns that exist in the source database. In order to create the identity columns, you will need to edit the mappings to open the dialog below and select “Enable identity insert.” Also, if you are running SQL Server Express, Web, or Workgroup edition, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.
Approach #2: Copy Database Wizard
You can use the Copy Database Wizard in SQL Server Management Studio to upgrade from a SQL Server 2000 database to a SQL Server 2005 or later database. If you are running a SQL Server Express edition, there are two potential issues in using the tool. First, the copy command is not available from the Tasks dropdown menu. Second, the destination server must be SQL Server 2005 or later. This means that you cannot copy a database from a SQL server to your local Express edition.
When I tried to use the tool to copy data from one SQL Server 2000 database with one hosting company to a SQL Server 2008 database at a different hosting company, the tool failed due to an error shown below: Object reference not set to an instance of an object.
I clicked on one of the two icons, and saw a bit more detail on the error. But the information wasn’t very helping in a solution. I tried several times but encountered the same error. So I decided to move on without wasting too much time. (I would like to find out the cause of the error, and will provide an update in another post if there is any finding I can share with you.)
Approach #3: Generate Scripts
My last resort was to use the “Generate Scripts” option in SQL Server Management Studio and at last, it worked. When using the script wizard, make sure you set the “Script Data” option to true. If you any triggers and indexes in the source database, set these options to true accordingly. To include stored procedures, you can select it from the “Choose Object Types” screen as shown below.
Before you complete the script wizard, you can choose to save the script for a file, which I did. You have other two options: script to clipboard or script to a new query window. You can find more detail from this post by Pinalkumar Dave, a SQL Server MVP and Database Administrator.
The script wizard worked well for small databases. For databases larger than 100 MB or 1GB, you may want to consider another approach: use the copy wizard to get a copy of the original database to your local SQL Server (not the Express edition). You can then copy it to from your local server to another database server. Or, send the database files to your hosting company and ask the tech support administrator there to help you attach the database to the database server assigned to you.