Tag: import

Migrating from SQL Server 2005 to SQL Server 2008

Basically, I needed to migrate a fairly simple database from SQL Server 2005 to SQL Server 2008 for a project I’m working on. The database has six tables, dozens of columns, properties and unique keys. There was also a stored procedure and a trigger just to spice things up.

I was quite happy with how easy and fast the migration went. I’ve had seemingly simple database tasks go sideways before and take hours to resolve. From beginning to end, this whole operation took about five minutes.

Here are the steps I used, although yours may vary slightly.

  1. Launch SQL Server Management Studio on your SQL Server 2005 machine
  2. Right click on the actual database then click Tasks > Detach. This step insures there are no active processes accessing the database.
  3. Since my SQL Server 2008 database was locked down, I copied the .mdf and .log files from the SQL Server 2005 machine over to the corresponding directory on the SQL Server 2008 machine via Remote Desktop. SQL Server does have an option to copy from one database directly into another. However, that involves opening ports in firewalls to allow access between the server, and I didn’t have that luxury for security reasons. 

    4. Note 1:  Make double sure you don’t overwrite an active database of the same name on the destination server!

    Note 2:  the default installation directory for these files on SQL Server 2008 is here: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

  4. Launch SQL Server Management Studio on your SQL Server 2008 machine.
  5. Right click on “Databases” then select “Attach”.
  6. This will launch the Attach Databases window. Select “Add” and navigate to the correct .mdf file that you just copied over.
  7. Follow any additional prompts.  I didn’t have any full-text catalogs so I just clicked “OK” when that pop-up window appeared.  For me , SQL Server 2008 automatically converted the old SQL Server 2005 database into SQL Server 2008.

If you follow these steps, and if there were no errors, you are almost done. Before you pop champagne and celebrate make sure you can access the database from SQL Server Management Studio. Open the tables and run a quick test query just to make sure. If you have a more complex database you might want to run multiple test queries, and run your application, just to make sure everything moved over properly.  If you have a migration story please share it here.