One of the very common task for DBAs is to move or migrate databases from one SQL Server instance to another SQL Server Instance. The moving of the databases between SQL Server instances are regularly performed due to various reasons which include databases refresh from production to UAT or Development or Test environments, migration of databases during upgrades, hardware replacements, etc. It is very important to understand the various methods available to move the databases between the SQL instance and steps required to move to safely and successfully move the databases. Also, it is important to have options to quickly and reliable way of rolling back the changes in case of any issues. Below are some of the methods and steps required for moving or migrating SQL Server databases between SQL Server instances.
Backup/Restore: This is very reliable method of moving or migrating SQL Server databases from one instance to another instance safely. This method also leaves the existing databases on old server thus the rollback is easy during the downtime window. In this method Full, DIFF and LOG backups of the databases are performed on the source SQL instance and the backup files are copied over to the destination SQL instance and restore all the backups in order with norecovery option and restore the last log backup using with recovery option which brings the database online. It is important to plan the type of backups to be performed on which times and prepare the sequence to restore the backup files. The amount of time taken to move the databases can be long if the size of the databases is big for which the backup, copy and restore of Full backup should be performed before the downtime and during the downtime, differential or log backup can be performed which will be small in size and can be moved quickly and restored quickly which will reduce the amount of downtime. This method is most widely used method for database refresh and migrations.
Detach/Attach: This is a good method, but involves lot of risk this not widely used. In this method, the databases are detached on the source SQL instance, the .mdf, .ndf, .ldf files are copied to the destination SQL instance and attached to the destination SQL instance. This method may take less time for moving the databases compared to the Backup/Restore, but has more risk of databases being unavailable on source server for long times. Also, if the databases contain any additional files like FullText or FileStream, then those files are to be copied as well and can become complex. In this method all connections to the source database has to be disconnected before the start of the task and will only be able to connect after all the steps are completed. This method can be used for small and non-critical databases.
Logshipping/DatabaseMirroring/AlwaysON: In this method, logshipping or database mirroring or AlwaysON Availability Groups is setup before-hand between the source and destination SQL instances and during the downtime the logshipping or database mirroring is stopped and removed and final log backup is performed on source and copied over to destination SQL instance and restored with recovery option which brings the SQL instance online. This involves very less downtime and the destination server can be prepared for migration well before and can continue even if the migration is post-poned for some days. This method is commonly used for migrating critical SQL instances from lower version to higher version or for migrating critical databases from one server to another server with less downtime.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.