A lot of companies out there are still on SQL Server 2008 (some are still using 2005!).  Now that SQL Server 2012 has been out awhile, I’m seeing questions on what it takes to migrate a database from SQL Server 2008 to 2012.  The bad news is doing an in-place upgrade from 2008 to 2012 is out of the question.  For one thing, many of you are still running SQL Server 2008 on Windows Server 2003 and SQL Server 2012 requires Windows Server 2008 Service Pack 2 or higher.  So you’re going to stand up a new server no matter what.  The good news is that it’s still easy.  In fact, you have two ways to do it.  Either perform a backup and restore or do a detach and attach.  This part covers backup and restore.

Backup and Restore

You can back up your SQL Server 2008 database and restore it to a SQL Server 2012 machine.  Keep in mind that I’m talking about user databases, not any of the system databases.

First, back up your SQL 2008 database.

Once your backup is complete, copy the BAK file over to your new server.  Once done, restore the database.  Note that you don’t have to create a database first just to restore over it.

Once your database is restored, you can set compatibility level options in case you have older applications that expect some type of older behavior.

Keep in mind that this option is not a panacea and that you should thoroughly test before just shutting off your old SQL Server 2008 machine to ensure current applications don’t depend on some old behavior or deprecated feature.  Also, do keep in mind that despite setting the compatibility level to SQL Server 2008, the database file format itself is still upgraded to SQL Server 2012.  This means you cannot move the database back to your old SQL Server 2008 machine.  If you need to move back, you’ll have to extract all the data that has changed since the last backup and import manually.  I know of no way to restore or attach a SQL Server 2012 database to an older version of SQL Server.

JamesNT