queries running during the sql backup

  • Hi,

    I would like to know answers for below questions. Kindly confirm the process during the sql backup.

    Backup of our application takes around 1.5 hours to complete, meanwhile the users are still able to access the site and make transactions.

    The query is that:

    1) Are those transactions are also being backed up in this?

    2) What this backup covers and does lock the database to take backup?

    3) Will it backup all the data that's being written while backup is in progress?

    4) Will there be any data loss when we roll back using this backup?

     

    Thanks,

  • (1) Yes

    (2) It covers the state of the database at the moment the backup finishes.  There are no locks on user objects, although you may find that wait times are higher due to IO pressure, depending on your throughput

    (3) Yes, unless the data that's written is subsequently updated or deleted before the backup is finished

    (4) You'd lose all changes made between the backup finish and the start of the restore.  Make sure you use Full recovery mode if you want to avoid that

    John

  • Hi,

    Thanks for your reply.

    What about uncommited transactions during the backup? Still update query is running but not yet commited. But backup is done. In this case how the process will work?

     

    Regards,

    Pol

  • My guess would be that since the transaction hasn't been committed, it wouldn't form part of the database backup... or at least if it did then it would be rolled back as part of the recovery process.  If it's important for you to know, then test it - it wouldn't be too difficult to set a backup going and then immediately start, but don't commit, a transaction.

    John

  • Yes. Easy to test. Create a tiny test database and leave an open transaction during backup. Then restore it and look at the data.

    "...If it's important for you to know, then test it - it wouldn't be too difficult to set a backup going and then immediately start, but don't commit, a transaction...."

    • This reply was modified 2 years, 11 months ago by  homebrew01.
  • When a backup starts, a marker is placed in the transaction log. Any transactions that are completed when the backup is done, are then backed up. Any transactions that are open when the backup is complete are not backed up (obviously). Then, when you run a restore, the recovery process at the end of the restore operation, deals with closed or uncommitted transactions to ensure that the database is internally consistent.

    Like everyone else says, don't take our word for it, test it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey wrote:

    When a backup starts, a marker is placed in the transaction log. Any transactions that are completed when the backup is done, are then backed up. Any transactions that are open when the backup is complete are not backed up (obviously). Then, when you run a restore, the recovery process at the end of the restore operation, deals with closed or uncommitted transactions to ensure that the database is internally consistent.

    Like everyone else says, don't take our word for it, test it.

    Amen to that... especially since there's no guarantee that the MS documentation is actually correct.  I've found it to be shockingly incorrect, especially recently.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply