SQL Server Performance

backup/restore vs detach/attach

Discussion in 'General DBA Questions' started by pcsql, Jun 21, 2005.

  1. pcsql New Member

    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.
  2. Luis Martin Moderator

    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.



  3. flachance New Member

    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...
  4. ranjitjain New Member

    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.
  5. dineshasanka Moderator

    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

  6. satya Moderator

    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.
  7. pcsql New Member

    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.
  8. ranjitjain New Member

Share This Page