Use Backup/Restore to Minimize Upgrade Downtimes

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?