Attach vs Restore Database in SQL Server: Choose the Best One
Summary: The blog will help one to learn very basic differences between attach and restore database in the SQL server. Through this post, we are delivering a clear-cut comparison between the two most popular SQL server operations i.e., Attach-detach vs Backup and Restore.
Both the described server operations are meant to transfer data from one MS SQL server to another. But, users get confused that for which activity should they go for?
Detach/Attach: This will be detaching database from the source server and then, one can attach or copy MDF and LDF files in the targeted server.
Backup/Restore: With help of this operation, take backup of the source SQL server database and restore it in the targeted server.
The above statements give an overview about the server migration activities but, the major problem is to decide the best one. Well, before learning the difference between attach and restore database in SQL server, let us have a look on the following scenarios posted by the server users:
“One of my colleague called me yesterday for asking a problem associated with transferring and restoring of a huge database. He actually wants to copy a database to another server and wants my opinion in choosing the correct measure. I am unable to answer him out. Please somebody give me assistance in the same so that I can help that person.”
“I want to upgrade from SQL server 2008 to 2016. Actually, I am getting confused in the fact that whether I should go for attach-detach option or for backup and restore. I am thinking to use backup and restore option but, it feels as if the procedure is going to consume lots of time. Talking about the another measure then, it is also going to consume time. My mind has got puzzled in deciding any one way. Is there any technician who can resolve this puzzle of mine.”
Difference Between Attach vs Restore Database in SQL Server
| Attributes | Attach/Detach SQL Server Database | Backup & Restore SQL Server Database |
|---|---|---|
| Description | The operation involves simple attachment of secondary SQL server database file with primary one | The operation involves restoration of all items from the SQL server database backup file. |
| File Format of Source SQL Database File | Here, the source file for attachment is in MDF format. | The SQL server backup file from which items are to be restored is in BAK format. |
| Required Mode | This activity is carried out in an offline mode. It means that the source database must be in inactive status. | It requires online or active mode for the successful finishing up of the task. |
| Total Time Required | The time taken in attaching the SQL database is as such not dependent on file size. Basically, it is same as the time required in copying data files and log from one server to the another. | Restoration time of database is equivalent to 3 times of the backup time. Talking about the migration from one server to another then, time will be overall combination of backup time, restoration time and then the time required in server migration. |
| SQL Server Log File Presence | If in case the size of server log file is huge then, users need to copy the all the records over the network. However, if you are not having the log file then also you can simply attach MDF file and continue with the server migration. | The backup file size does not include any kind of data log file size aspect. |
| Fragmented Data Files | It would be of no worth, if you are carrying attach option on fragmented data files. It is so because this activity will carry unwanted additional data bytes, which are totally of no use. | The SQL BAK file comprises of the pages, which are already used in the server. So, here the size concern is quite close to the size of used data files. |
| Maintenance of the SQL Server Activity Record | There is no record maintained for the detach or attach operation in the SQL server. So, no procedure details like procedure time, where were the files attached, etc., are stored in MSDB database. | The procedure of backup and restoration in the SQL server is stored in MSDB database tables. This table comprises of information like date, size, backup / restore type, etc. |
| Equipped Additional Options | No other additional options area available to move data from one server to another. | Users are provided with advance options like partial backup, mirrored backup, time recovery point, etc., in this migration measure. |
Conclusion
All the possible differences are explained under different circumstances that will help you in easily choosing the best activity. Always read differences between attach and restore database in the Microsoft SQL server to properly understand them. After clearing all doubts, one can now decide with which they want to go for.