locked
Performing Database backup while database in use (insert/update/delete) RRS feed

  • Question

  • I know I've read that you can easily perform an "online" backup.  My question is how are changes handled to the live data while a backup is being formed.

    Example is 20 people update tables or delete records within a database during the START of a database backup, will those changes reflect once the backup is finished?  My thinking is they will be written to log but never appear in the data until the backup is finished.

    I'm backing up a rather large database (800gb).  

    Friday, October 10, 2014 7:36 PM

Answers

  • You will find the answer here:

    http://serverfault.com/questions/100490/what-happens-during-a-live-sql-server-backup


    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    Friday, October 10, 2014 8:33 PM
  • I know I've read that you can easily perform an "online" backup.  My question is how are changes handled to the live data while a backup is being formed.

    Example is 20 people update tables or delete records within a database during the START of a database backup, will those changes reflect once the backup is finished?  My thinking is they will be written to log but never appear in the data until the backup is finished.

    I'm backing up a rather large database (800gb).  

    Hello,

    What you are asking is completely mentioned in Below article by Microsoft

    http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx

    Backup operation opens its own thread to read database from disk and write it to destination disk It almost in all cases does not causes blocking. If backup operation has already read the page you are changing the change would not be in backup. If you made change to a page and that change committed before backup database operation completed , it would make sure that change reflects in backup by taking required amount of transaction logs so that when you restore backup it would bring database to consistent state.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, October 10, 2014 9:01 PM

All replies