As a SQL Server professional, at some point in your career, you will need to upgrade between versions of SQL Server, or move a database from an older server onto a newer one. There are quite a few different ways to go about doing this, the most common being; Detach/Copy/Attach and Backup/Restore. When downtime is acceptable, either of these methods can get the job done, the only caveat being that if you are performing a upgrade to a newer version of SQL Server and you decide to use Detach/Copy/Attach to upgrade the databases that you still should take a backup of the database before moving it so that you have a point to fall back top. Once you attach the database files to the newer version they will be upgraded internally and will no longer be able to be used on the older version.

If time is of the essence during the migration, and downtime must be minimized, the best approach will depend on the size of the database being upgraded. For a database that is under 4GB in size, it may be acceptable to still do a Detach/Copy/Attach move of the database, but for a database that is 40GB in size, the time it takes to copy the files to the newer server could exceed the allowable downtime for the system. If the database is 400GB, it will most certainly take to long to move the database by Detach/Copy/Attach. In this case the best path to migration/upgrade is to work with Backup/Restore.

So how do you go about doing this? Its really not that difficult at all. First you will need to place the database that you are planning to move/upgrade into FULL Recovery if it is not already in FULL Recovery. To determine if your database is in FULL Recovery, run the following query:

select recovery_model_desc 
from sys.databases
where name = 'AdventureWorks'

If your database is already in FULL Recovery and you are doing Transaction Log backups, skip to the next paragraph, otherwise, set the database to FULL recovery with the following command:

ALTER DATABASE AdventureWorks 
SET RECOVERY FULL

and then take a FULL backup of the database to disk. While the full backup is running, create a Maintenance Plan task to perform Transaction Log backups of the database at regular intervals. Set an interval that will prevent your Transaction Log from filling up between Log Backups. Depending on how busy the database is, and how large the Transaction Log is, a Log backup every 1/2hr to 2hrs should probably work for you.

Copy the most recent full backup of the database onto the new server and restore it using the NORECOVERY option which leaves it in a Restoring mode, and allows you to apply a Differential Backup and/or Transaction Log backups to the database to roll it forward in time. Depending on how long it takes to copy the full backup file to the new server and restore it, you may want to take a Differential backup of the database to minimize the number of Log backups that have to be moved and restored to roll the database forward to the current point in time. Make sure that as you apply any Differential or Transaction Log backups that you continue to specify the NORECOVERY option leaving the database ready to accept further Log Backups.

When the time comes to perform the cut over to the newer server, you should have applied all of the Transaction Log backups from the old server which leaves you with a small amount of change to actually transfer to the new server. To perform the final cut over, first stop the applications that utilize the database, or put up a maintenance page if the database services a website to prevent further changes to the database from continuing to occur. Once you do this, the clock is ticking because for all intents and purposes, the database is now down.

Perform a final Transaction Log backup on the old server (this is called the tail log backup), and then take the database offline if it is a shared server, or shutdown the SQL Services if it a dedicated SQL Server for this database. Copy the tail log backup onto the new server and restore it specifying the RECOVERY option, which recovers the database and brings it only. Perform any application reconfiguration necessary to point to the new server, and then start the application back up, or remove any web maintenance pages, returning the application to full use.

If you do this correctly, you can move/upgrade any size database with only a few minutes of actual downtime. To recap, the steps for this are as follows:

1. If the database is not in FULL recovery change it to FULL recovery.
2. Take a full backup of the database.
3. Create a Maintenance Plan to perform regular Transaction Log Backups of the database.
4. Copy the full backup to the new server.
5. Restore the full backup on the new server specifying NORECOVERY.
6. Copy any Transaction Log Backups that have been taken from the old server to the new server.
7. Restore Transaction Log Backups on new server specifying NORECOVERY.
8. Shutdown applications using this database or put up a maintenance web page if it is for a website.
9. Take a final (tail log) Transaction Log backup on the old server.
10. Take the database Offline, or shutdown the SQL Server.
11. Copy the tail log backup onto the new server.
12. Restore the tail log backup specifying the RECOVERY option.
13. Perform any reconfiguration of the application/website.
14. Start the application or remove the maintenance web page restoring service.

Start to finish the process took me two days when upgrading my largest database. However, the actual downtime experienced by the application was under 10 minutes, most of which was used reconfiguring the application.

When minimal downtime is essential, this process should be used over Attach/Copy/Detach of the database because it allows the majority of the data to be copied and restored on the new server while the old server is still servicing the database. These days it is not uncommon for SQL Servers to have multi-Terabyte databases that require minimal downtime. It doesn't matter if the database is a few gigabytes or many terabytes, this process can be used to minimize upgrade/migration downtimes.