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,
December 10, 2021 at 1:14 pm
(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
December 10, 2021 at 2:06 pm
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
December 10, 2021 at 4:31 pm
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...."
December 13, 2021 at 2:50 pm
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
December 13, 2021 at 6:39 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply