July 26, 2019 at 6:36 am
Hello everyone.
I have I question, maybe someone know best practice.
We have SQL Server 2014 database with failover cluster with 2 nodes and this cluster with standalone SQL Server 2014 in AlwaysOn HA in asynchronous mode. Every evening we have business procedures like End Of Business Day (named COB). Databases in Full Recovery Mode. And we need to take backup before COB and after COB. But we can't turn off application and we need database backup without transactions during taking backup. As I know, when database in full recovery mode, it include transaction logs which append during backup process (like if backup goes 1 hour, all transactions during this 1 hour will be added to backup). Is there a way to exclude this transactions or to take backup only until start LSN. We take weekly full backup and daily diff
July 26, 2019 at 12:24 pm
You can't take a full backup without transactions. It's fundamental to the recovery aspect so that it can successfully restore without corrupting the database. Not only do can you not remove that aspect of the backup, you really don't want to.
If you're trying to minimize the size of the backup, then run a log backup right before you run the full backup. You have the databases in full recovery so this means you're running log backups, right? Run another one. Log backups chain together very nicely.
"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
July 26, 2019 at 3:59 pm
Thanks for reply. But I don't care about size. My problem is state before COB. Before COB backup must not contain any transactions, because by our business policy, if we need restore we must restore to point before COB (without any transactions during backup process)
July 26, 2019 at 5:25 pm
Thanks for reply. But I don't care about size. My problem is state before COB. Before COB backup must not contain any transactions, because by our business policy, if we need restore we must restore to point before COB (without any transactions during backup process)
The only method that I know of where you can take a backup with a guarantee of no transactions occurring is to set the database to the single user mode and do the backup using the same SPID.
Of course, that isn't what people will ever refer to as an "online" evolution because it will not allow other users even read access to the database.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2019 at 6:43 pm
Restore to a point in time. That's all you have to do.
"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
July 26, 2019 at 6:47 pm
Restore to a point in time. That's all you have to do.
Now THERE's an idea!!! That's why they pay you the big bucks!
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2019 at 6:48 pm
Grant Fritchey wrote:Restore to a point in time. That's all you have to do.
Now THERE's an idea!!! That's why they pay you the big bucks!
Whoa! Hold on a sec. I can get paid for this stuff?
"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
July 26, 2019 at 6:56 pm
Thanks for reply. But I don't care about size. My problem is state before COB. Before COB backup must not contain any transactions, because by our business policy, if we need restore we must restore to point before COB (without any transactions during backup process)
This doesn't actually make sense...by transactions are you referring to end user transactions? If so - then you need to lock out the users prior to starting the backup (how that is done depends on your application). If not - and what you really need is to insure that you can restore to a point where the state of the system was prior to the COB - then run the backup and once it has completed start COB.
If you have to recover - you recover to the backup taken prior to the COB...
Now - if you just need the ability to recover to the point in time right before the start of the COB, then you would restore the backup prior to the COB (doesn't matter how much prior) and then apply all transaction logs up to the point in time you want to recover - specifying the actual point in time. At that point - your system will be recovered to a consistent state just prior to when the COB was started.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2019 at 6:35 am
Thanks everyone for replies.
We can't set DB to single user mode. Because application must work and transactions must be commit. We thought about restore point in time variant and it was the one thing what we can do. I think maybe there are another ways to take backups without transaction logs.
July 29, 2019 at 10:17 am
I think maybe there are another ways to take backups without transaction logs.
You can think over database snapshots , if COB backups are needed only in case of rollback on the same DB server .
However, having proper backup process in place allows you to restore the db to any point in time and demand of having pre-COB and post-COB is excessive.
July 29, 2019 at 11:19 am
I strongly recommend just focusing on the point in time recovery as your mechanism. It's easy to set up. Completely supported in every way. You won't have to do anything funky or odd. It's exactly what you need with no additional processing on your part. I'm not entirely sure why you'd want to continue trying to find a way to create a backup on a live database that is in some way going to be able to successfully recover while active transactions are running without taking those transactions into account. I'm positive that you're not going to be able to find something that does that.
You have two choices. Deal with active transactions using point in time recovery (exactly what it's designed for), or, remove all transactions by stopping them from occurring (single user mode) and then take a backup. There really isn't a 3rd option that I'm aware of.
Even if you use third party tooling, they have to take into account active transactions because data that's in a half-completed state will not recover. Even hardware solutions that snapshot the disk take into account transactions (well, the ones that work do, there are ones that ignore transactions, but then your databases don't recover).
It's all about dealing with the fact that you have active transactions in the database. For recovery, these MUST be taken into account. Fighting against it is a losing proposition.
"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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply