You want to move a database from one server to another. There are two options to do that.
1) Detach/Attach: To Detach the database from source server and copy the Data and log file ( MDF and LDF ) of the database and attach it in the destination server
2) Backup/Restore: Perform a SQL Backup of the database and move the
backup file to destination server and Restore the database in destination server.
A simple comparison of both the methods.
Detach/Attach | Backup and Restore |
Detach/Attach is a offline operation. Source database will be inactive when you perform detach and attach operation | Source database can be accessed as it is a online operation |
Detaching and Attaching the database happens instantly irrespective of the size of the database.Time taken in migrating the database is same as the time taken for copying the data and log files from one server to another | Backing up a database can take considerabale amount of time ranging from few minutes to many hours depending upon the database. Restore of a database on a average takes about 3 times of backup time. So, total time taken in migrating the database would be backup time + restore time + time taken to move the backup file from one server to another |
Sometimes, if the Log file size is huge then one needs to copy large amount of data over the network | Backup file contains only used data pages in data file and hence the size of the backup file would not include the size of data log file |
Sometimes, if the Log file size is huge then one needs to copy large amount of data over the network | Backup file contains only used data pages in data file and hence the size of the backup file would not include the size of data log file |
If the data file is fragmented/ If the Data file has lots of free space with in the file ( allocated but unused ), then copying the data file would mean carrying additional bytes of data though the they are unused. | Backup copies only used data pages of the data file. So, the size of the backup is always close to the size of the size used with in the data file. |
Detach / Attach is not recorded in any table in msdb database. so one has no record of who detached/atatched who or when was it done, what were the files detached, what size or where the files are stored etc. | Backup and restore operation details are always stored in msdb database tables with information like size,date,location,type of backup / restore. |
Advanced options like mirrored backup/ partial backup/ compressed backup/ backup to tape/ point in time recovery are not available | All advanced options are available |
Detach and attach method is useful when one wants move the database fast, without caring much about the availaiblity of source server.Backup and Restore is certainly a much graceful way to do the same.
