SQL Server Performance Forum – Threads Archive
backup/restore vs detach/attach
What is the main difference between backup/restore a database and detach/attach a database? Is there any difference in terms of security/permission after a database is restored comparing to one which is attached? When should I use backup/restore instead of detach/attach or vice versa.Thanks.
Backup is on line process. You can’t detach when database is in use, but you can backup it. I use attach/detach when I have to change one database to other instance, because is faster than backup. Also, you have differents way to backup one database, like full, differencial,etc. I generals terms I suggest to use backup when you need to "backup" databases. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
When you have to move a database from one server to another one, I prefer to use the backup/restore method. Especially now that SP4 has made the management of users simpler. Before you had to go and mess around with system tables. The nice thing about the backup is that you end up with only one file. If you have a database with multiple files (to spread the I/O on multiple physical drives), then the attach/detach method is more work. Just my two cents…
Adding more is attach/detach things should be done properly else sometimes its get difficult to make it online.
well detach/attach requires actual data file i.e. MDF file whereas backup/restore option makes a single file with all the data and script of object noted which is generally much safer.
Both the above are relatively differentand even use is different.
when u detach the DB can not be accessed i.e. SQL make it offline and then detaches from database.
backing up still keeps the DB accessible.
Yes there are many limitations with the attached/detached. I am using attached/detached in order as the a quick way of droping the log file. but before that I will take a backup
True and until unless you’re sure about having downtime do not use detach method.
If both the servers are similar then using BACKUP/RESTORE method is ideal and managable.
Only in the test environment I use attach/detach method and in production always go by backup/restore method. RESTORE and DETACH have similar process when recreating the database and until it finishes the database will not be accessible. With RESTORE you have couple of options to make database available. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
quote:Originally posted by flachance
When you have to move a database from one server to another one, I prefer to use the backup/restore method. Especially now that SP4 has made the management of users simpler. Before you had to go and mess around with system tables.
The nice thing about the backup is that you end up with only one file. If you have a database with multiple files (to spread the I/O on multiple physical drives), then the attach/detach method is more work.
Just my two cents…
Can anyone elaborate more about the SP4 enhancement mentioned here?
Thanks.
hi pcsql, check this for sp4:
http://www.microsoft.com/sql/downloads/2000/sp4.asp
]]>