SQL Administration

Administering SQL Server

SQL Server: Migrating a Database from version 2008 to 2012 Part II

JamesNT SQL Administration, SQL Server ,

In our last post, found here, we used the backup and restore functionality of SQL Server 2008/2012 to move a database from one version to the other.  Be reminded this is a one way transfer.  The SQL Server 2012 version of the database cannot be moved back to 2008 to the best of my knowledge.  Even though you may set compatibility to stay at 2008, the format of the database files is changed.

Today, we’ll look at the second way to move a database from SQL Server 2008 to SQL 2012:  Detach and Attach.  Note that this is not the preferred method of moving a database from one version of SQL Server to another – or even to another SQL Server of the same version.  The reason is this method involves downtime.  When you detach a database, connections to that database may be dropped and no one can connect until the database is reattached.  Let’s take a look.

  image

Note that you can forcibly drop connections to the database.  You may need to do this during a detach as if there are any connections, the detach will fail.  Keep in mind that many Line-of-Business applications do not like having their connections forcibly closed on them and may themselves crash.  Also note that once you detach the database it will no longer be listed in your database list.

image

Once the database is detached, you can using Windows explorer to copy the database files to the new location on the new server.  Once the files are there, you can attach them to the new server.  If you still need the database active on the old server, don’t forget to re-attach it using the same procedure shown below.

image

image

You may need to set file paths.

image

Once you click OK the database will be attached.  Again, be certain to note that the database file format will be upgraded making it unusable on former versions of SQL Server.

As noted previously, this is not the preferred way to upgrade, or even just relocated, a database to a new server.  It does involve downtime and you may have to kick people off for it to work.  The preferred method, in my opinion, is to do a backup and restore.

JamesNT

SQL Server: Migrating a Database from version 2008 to 2012 Part I

JamesNT SQL Administration, SQL Server ,

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.

backup20081

backup20082

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.

2012restore1

2012restore2

2012restore3

2012restore4

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

image

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