SQL Server Performance Forum – Threads Archive
detach->attach —– restore
Folks, just curious, when restoring a 20GB database, it takes around half hour, while if detach and attach, it takes several seconds. What is the internal difference between these two database copy method? based on my understanding, de-attached just did some repointing work, which part controls this? Thanks. ——————Bug explorer/finder/seeker/locator
——————
The big difference is: when you backup a database the database remain in your SQL. When you dettach there is no database in you SQL. See more differences in BOL. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
You can say…Backup is online operation and Detach is offline operation… Detach takes few seconds but you have to copy the files (.mdf and .ldf) to different server to attach to make the db available which will take almost the same amout of time the restore is taking place…
Mohammed U.
You may find little bit difference in the performance if you update stats before detaching the database, as far as the backup is concerned it has to lookup for all the changes that are updated from the last backup and that will have difference in performance you have noticed. But both of them always work in different ways and backup doesn’t need any outage, but attach/detach should need. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Thanks, folks, Actually I want to know the internal activity, such as, For restore, database engine will _______________________________? For detach, database engine will ______________________________? For reattach, database engine will ___________________________? ——————
Bug explorer/finder/seeker/locator
——————
When you make a backup and restore it, the data is: 1. Copied from the data files into the backup file (backed up) 2. Copied from the backup file into new data file(s) (restored) If you instead detach the files, they are basically just closed (there’s probably some handling for pending transactions); Likewise, when attaching them, they are basically just opened again, with some process that resolves the transaction log to a consistent state. So backup and restore can take some time to re-write the data, while detach/reattach probably happen faster on a single machine. On the other hand, a SQL Server data file sitting on a drive is quite likely to have some percentage allocated but empty space, which is not present in a backup file — so the data file is probably bigger than a full backup of the same system, unless you perform a shrink. The difference in size could have an impact if, for example, you plan to copy the data over a network connection to another machine. Does that answer the question?
Thank you, merrillaldrich. You exactly answered my question. I appreciate it.
——————
Bug explorer/finder/seeker/locator
——————
]]>