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.

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.

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.

You may need to set file paths.

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