In recent times, database migrations have become a way of life for most database administrators. Whether moving to new hardware or into the world of virtual machines, what once was a rare project is becoming an almost everyday concern for database environments everywhere. At Xtivia, many of our clients request we help them migrate databases to new environments either because their current hardware is obsolete, they are consolidating their database environments or they simply wish to upgrade to a newer database engine even if they do not necessarily want to specifically update individual databases.

Now keep in mind, for this example we are ‘moving’ user databases to a new environment. We are not making a replicated copy for report service or high availability. Those processes have considerations of their own that are not discussed in this article. Instead, assume this scenario, we have a set of mission critical databases that we wish to integrate into a newer database engine environment. These databases will be moved from a SQL Server 2005 environment to a SQL Server 2012 environment on a newer server with more horsepower. At the end of this process, the server that previously hosted these databases will retired in favor of the newer environment.

So how do we begin? The first thing we need to do is document exactly what needs to be done in order to complete this project successfully. I know that we have all been drilled with the importance of documentation but I can’t emphasize this enough. On many occasions, my services have been called upon to address migration problems and one of the most common problems is that the migration was attempted without a valid plan for success and critical steps were overlooked. Please save yourself the headache of a migration fraught with problems by outlining what needs to be done and who is responsible of carrying out each task. I suggest using any one of a handful of software programs that can help you define project steps and manage those steps to a successful conclusion.

That being said, the question is exactly what the correct steps are in order to successfully move a production environment to an improved environment.  There are many different methodologies available for use for a migration but I tend to use the detach/attach method.

The detach/attach method simply detaches the database from one SQL server engine and attaches it to another. It is a simple and effective way to migrate from one instance of SQL Server to another. This method keeps the database in the exact condition on the new instance as it was in the old instance, including all objects and users that are part of that database. Using this method, it is not necessary to script procedures, indexes, views or the like. All of those things are still contained within the database. However, there is still a need to move other facilities, such as SQL agent jobs, maintenance plans, replication publications and other features that are not actually included in the database file.

Here is a rundown of the steps I usually take to migrate databases to a new environment:

  1. Plan on Down Time

One of the most difficult aspects of database migration is reconciling the fact that there will be down time involved regardless of the methodology we use. However, proper planning can minimize the down time involved. In the detach/attach methodology the primary time consumer is usually moving the data and log files to the new server. The time that task will take depends on a variety of factors including network speed, file sizes and so forth. Other factors that may cause downtime are rebuilding maintenance plans, jobs and so forth. The point is to plan for some amount of down time during the database migration.

  1. Rehearse the Migration

The best way to judge how much downtime will be involved is to rehearse the migration in a development or test environment prior to moving production databases. This not only will give you an approximation of the time needed to complete the migration but will also usually help pinpoint any roadblocks you may encounter during the production move.

  1. Check Database Compatibility

One important step that is often overlooked is checking the database compatibility level. Remember, we are migrating, not upgrading your databases. These are very different things indeed. It is possible that your database may not be compatible with your new instance. To check the compatibility level, run the stored procedure ‘sp_dbcmptlevel’ on each of the databases to be moved. That procedure will report whether or not your databases are compatible with the new version.

  1. Backup All Data and Log files

I always recommend that a fresh backup be taken whenever any significant work be performed in a production environment. However, this is especially essential when migrating a database from an early version of SQL Server such as SQL Server 2005 to a later version such as SQL Server 2012.

Once we attach the database to the newer version, SQL Server changes the database to conform with the new version’s requirements. There is no backward compatibility once this is done and the database will not re-attach to the old instance if a rollback becomes necessary for any reason. If we ensure we have a fresh backup, we can always re-create the databases restore the backups in the old environment.

  1. Quiesce the Databases

What does that mean?!? In short we want to ensure that no one will be able to access the databases while we are in the middle of the migration. To do this we need to put the database in ‘Single User’ mode. To accomplish this:

  1. Right-click the database you wish to change
  2. Choose ‘Properties’
  3. In the left menu, choose ‘Options’
  4. Scroll down to the ‘State’ section
  5. On the line stating ‘Restrict Access’, choose ‘SINGLE_USER’ from the drop down list.
  6. Click ‘OK’

Or use this script:

USE [master]
  1. Detach the Databases

Now we need to move the databases to the new environment using ‘detachattach’.


  1. Right-click the database you wish to detach
  2. Choose ‘Tasks’
  3. Choose ‘Detach’
  4. When the wizard appears, click ‘OK’ (do not check any of the checkboxes at this time).

Or use this script:

USE [master]
EXEC master.dbo.sp_detach_db @dbname = N'<DBName>'
  1. Move the Database

Now we need to move the data and log file to the desired location. This can be any location we wish so long as the new instance will have uninterrupted access to that particular hard drive. This is usually the most time consuming task. Depending on the file sizes and network speed and activity, this could take quite a while.

However, physically moving the data and log files will only be necessary if the data and log files are on the same server as the instance that will be retired. If these files are on a SAN or other hard drive that is available to both the new and old instances, you can simply detach and attach the files from their current location.

  1. Attach the Database

Now we can attach the database to the new instance.


  1. Right-click on ‘Databases’ in the new instance
  2. Choose ‘Attach…’
  3. When the wizard appears, click ‘Add…’
  4. Navigate the location of the data file
  5. Highlight the data file you wish to attach
  6. Click ‘OK’

Or use this script:

USE [master]
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA<DBName>.mdf' ),
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA<DBName>._log.ldf' )

Notice that when you choose the data file it will attempt to find both the data and log files. If you have moved the log file to a location that the wizard cannot automatically find, use the browse button next to the ‘Log’ file type and navigate to the location of the log file before clicking ‘OK’. However, a new log file will be created if you do not designate one.

  1. Move the Logins

Now that the databases are in the new environment, we must move the logins from the old instance to the new instance. The problem is that if we simply re-create the logins, the SPID used to identify the login will not match the user contained in the newly moved database. Remember, that login was created in the old instance.

However, all is not lost. Microsoft does have a process that we can use to move the logins, including their SPID that matches the database but also preserves the user’s password. That lets this process be virtually invisible to the user community. This process includes two stored procedures, ‘sp_help_revlogin’ and ‘sp_hexadecimal’. Click on either of the stored procedure names to navigate to the Microsoft page containing these procedures and instructions on how to use them. Its quick and easy and bypasses the nightmares usually associated with migrating users. It really works.

  1. Change the Database State Back to Multi-User

Not you should follow the steps outlined in item 5 to return the databases to ‘Multi-User’ mode.

  1. Additional Tasks to Complete

Congratulations! You have just migrated your databases from an old instance to a new instance. But wait before you celebrate a job well done. There are still additional tasks that should be performed before the job is complete. You will need to re-create the maintenance plans and jobs and change the compatibility level of the databases. It is also a good idea to update the database usage and statistics. You will also need to re-create any mirroring or replication if you need to maintain high-availability options. Also, to ensure that the databases are in good health, you should run DBCC CheckDB and check the database options to be sure that those options match the old configurations where applicable.

  1. And We’re Done

Now that your tasks are complete, you can tell your network and application folks to perform whatever tasks that they need to perform to point to the new instance.

Now that we have finished all of the clean-up tasks after the migration we are free to take to old instance offline. But before you do, unless there are urgent considerations, I suggest you leave the old instance live for a while to be sure that everything is where is should be. It’s just good policy to verify everything is good before the old instance is dropped.

Please keep in mind that in this article we are discussing the most simple scenario possible for a database migration. It should be noted that database migrations can become extremely complex. Here we are discussing a straight forward technique that I have used many times in migration scenarios but by no means is this meant to be a comprehensive discussion on every aspect of database migration.


Migrating a database or many databases to a new environment can be a daunting task but with a bit of planning, it does not have to be something to be afraid of. As always, XTIVIA is always there to help you through the process from start to finish.

Share This