Home Server Database Administration Solution to: How do I move a Microsoft SQL Server Database to...

Get social!

Please note: I am not a Microsoft SQL Server guy. I'm not really a Windows guy, and more. I use Linux + MySQL, mostly (I've been into MongoDB lately). The advice here should be considered suspect (it worked for me).

I needed to migrate a 2008 MS SQL Server DB from an old Windows Server 2000 (32-bit) computer to another computer running Windows 7 (32-bit). The server was running SQL Server 2008. I tried using SQL Server 2012 on the new server first. A simple backup and restore did not work. I received an error about incompatible versions. After googling around, I found that I could either write a script to export and import the data and schema (hours of work), or just use 2008 SQL Server on the destination computer (an hour of work). The second option was fine with me. The client had no specific needs that would require the 2012 version of SQL Server.

Solution

This is the nitty-gritty version of what I did to move the database.

  1. Download 2008 SQL Server Management Studio Express, install on new computer. You can find the installation file here:http://www.microsoft.com/en-us/download/details.aspx?id=7593 
  2. When running the installer, I received a warning about a known problem with the app. The problem is rectified by installing the latest SQL Server service pack (after installing the above app). Service packs are here:http://support.microsoft.com/kb/968382
  3. Next, on the server hosting the current database, do a backup. If you are not sure how to backup or restore a database, check out this article:http://kb.eukhost.com/backup-and-restore-ms-sql-server-2008-database/
  4. Restore the backup to the new SQL Server.

It was that simple. I should note that "Windows Authentication" was used on both database instances. I also needed to add users to the new computer and update the ODBC settings on the client PCs to allow for proper connectivity and authentication. That work is beyond the scope of this post -- sorry.

Leave a Reply

Bad Behavior has blocked 22 access attempts in the last 7 days.