April 12, 2020 at 3:32 am
Hi,
I have backups scheduled with a Full Backup (with the INIT and SKIP commands) weekly, Differentials Daily, and Log Backups hourly. These all use the same backup device/file. My question is, because I am using the INIT and SKIP options on the Full backup (to keep the backup file size manageable), am I at risk of complete data loss if my database fails during the Full backup?
When I say fail, I mean the database itself is destroyed...not the backup job failing. For example, let's assume the Full backup takes 4 hours. 2 hours into the backup, the database server "goes bang". Have I also lost the previous weeks Full backup, the Differentials and the Log Backups because it "went bang" after the backup started. Or is the backup operation itself on the backup device/file somehow "transacted" and not committed until the end?
April 12, 2020 at 2:14 pm
My guess would be yes, you'd lose it, but if you're going to go down the risky path of reusing backup devices, then you really ought to test it for yourself for your own peace of mind.
John
April 12, 2020 at 3:35 pm
Even worse - are you using INIT on the backup device for your differentials and transaction log backups? If so - then you are already at risk for significant data loss in the event something happens.
To restore a system to a point in time - you must have a full backup and *all* transaction log backups from that backup to the point in time you want to recover. As soon as you INIT the transaction log backup device...you break the log chain.
Now - if you are not using INIT on those...but rather trying to perform an INIT after the full backup it still isn't safe. As soon as you perform an INIT on the file you lose everything prior to that point in time. The most important part of this is that you no longer can recover from a known good backup prior to the current available file.
Example - let's assume your full backup occurs at 10pm on Sunday night. Your differentials occur at 10pm on every night except Sunday night - and your transaction log backups are set to run every hour on the half hour. The full backup takes 4 hours...and the system crashes at 11pm.
To restore the system you would need the backup taken from the previous Sunday - but you have initialized the file. It no longer exists...hopefully you have that file copied off the server. If not you cannot recover that database. Okay - let's assume you have the file and you restore the database - you restore from the differential which is the latest available and try to restore your transaction logs...but since you initialized the device you only have the data that was backed up at 10:30pm and that only contains the transactions from 9:30pm through 10:30pm...
Here is another problem - same scenario but you find out that the differential backup device was also lost and is corrupted. And the issue occurred at 11pm Sunday night. You do have the previous file...so you restore from that file...but you don't have a differential file nor do you have the full log chain of transaction log backups available and you now have lost a full week's worth of data.
If you are going to use backup devices...you need to insure that those files are copied immediately off the system to separate storage and date stamped. Without a clean chain of backups your recover options become very limited to almost non-existent. Would be much better to sacrifice a bit of storage to insure you have a clean set of backup files.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2020 at 4:44 pm
I strongly recommend ola hallengren's "SQL Server Maintenance Solution"(https://ola.hallengren.com/). You don't need to worry about the options, there is a history of full/differential/log backup instead of overwriting existing ones. Yes, it needs storage, but without a history, there is no guarantee the next one will be good before you overwrite the previous one. And you definitely need log backup chain for point-in-time recovery(for databases in full recovery model)
April 12, 2020 at 11:37 pm
Thanks for the feedback...that really helps me heaps. I'm performing the INIT as part of the Full Backup only, which, from what I can tell, is before the actual Full backup takes place. So when things don't "go bang" I end up with a backup device which starts with the Full Backup as the "first entry", and then the latest Differential next, followed by the Log backups.
This meets my needs in terms of "backup history". I don't have a requirement to restore prior to the last backup operation...I will only ever need to restore to the latest point in time possible. It only occurred to me recently that my main exposure when during the Full backup itself....and your feedback has confirmed this for me.
The way I see it my options are:
1. Copy the backup device to another location prior to the "INIT" command to protect me from failure during the full backup. I'd be able to delete that "safety copy" after I've confirmed the current Full backup was successful
2. Use (and learn about) expiring the backups in the backup device. If I set a 1 week expiry and stop using the INIT command, I assume the backup device will not grow in size for ever.
3. Alter my backup plan to alternate to different backup devices (odd and even weeks for example) so I would have the previous weeks backup in the case of my "big bang" failure scenario.
I guess option 3 is almost the same as option 1. My problem is the databases are large, and there are hundreds of them, so I have to work within the bounds of my storage (capacity and performance). I could also adapt option 3 to delete the "other weeks" backup after I've successfully performed "this weeks" full backup if I am tight on storage.
I'll be checking out the "SQL Server Maintenance Solutions" scripts...from first glance, I think it might make life easier for me...
Garry
April 13, 2020 at 10:27 am
It's a very good idea to have a second copy of your backups anyway. Not all of them every time, but, say, weekly or something. Just so, in the event of a real catastrophe, and not simply an issue using INIT, you have most of the data.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 13, 2020 at 4:25 pm
You are at risk. You are assuming your full backup file is actually correct and not corrupt. If you restore it regularly, that's better, but image you back things up, then at 5pm the database needs a restore. You start and your full backup file is corrupt or damaged.
I would keep 2 full backups, always. I use INIT, but I never write to the same file. Always a new name with a timestamp in the name. Then keep ALL backups since the oldest full backup. Once you have a new full backup, trim the oldest full backup and files up to the next full backup.
Today: Mon, 13 Apr, I'd keep:
When the backup completes tonight, 13 Apr. I'd delete these:
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 13, 2020 at 4:31 pm
So just did a quick test of what it sounds like you're doing (backing up Fulls and Diffs to the SAME backup device file) and you need to stop doing this ASAP.
My test was, I set up a backup device, a full backup to this device of my test database, using the following WITH options:
WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
Once that backup completed, I then created a garbage table (so something changed,) and ran a differential of the database with the same options and adding DIFFERENTIAL to the list.
Finally, I went to try to restore the backup...
Yeah, when SSMS reported "No full database backup is selected to be restored." that kind of told me that the INIT wiped what was already in the file.
Now, your last message indicates you're not doing INIT on the Diffs and Logs, but the paranoiac in me would still suggest going to one file per backup (don't even bother with creating a backup device,) using the Ola scripts. Why? Yes, you'll end up with a LOT of files instead of a nice neat 3 or so, but it also means you're more likely to be able to recover in the event your server does go "bang," especially if those backup files are getting copied / backed up off-server (and, even better, off-site in case the building goes "bang.")
April 13, 2020 at 11:32 pm
Thanks team for your input. I'm definitely looking toward keeping 2 copies of the backups (current device plus previous weeks device) to protect things as much as possible. I'm just discussing with the storage "guys" to ensure they can allocated the extra storage requirements...not that they really have much choice I guess...
Since my previous post, I've learnt that "Expiring" the backup sets contained in a device doesn't really help much, so I'm not going down that route. In fact, I'd say it gives more risk if I somehow worked out how to "purge" expired backup sets, since every time the device is opened for a read/written allows more chance of a corruption. I figure the less activity on a device the better.
My plan is to perform the weekly Full backup to a new device/file. Just prior to that Full backup job, the SQL Agent will rename the previous weeks *.bak files (there are hundreds of them) to something like *.oldbak. That to me is low risk...I'm just renaming files, not opening nor reading nor writing to them. This step will also delete the *.oldbak files from the previous weeks Full backup.
Then the Full backup starts, and it will create new .bak files as it goes through the databases.
With regard to the testing by @jasona.work (thanks for spending the time on this), something to note is that I'm not using the INIT,SKIP options on the Differentials (or the Log backups for that matter), so the device does still contain the full backup.
I have had to restore databases from these backups, so I'm confident the backup devices work, I just need to remove the risk of the failure mode I originally posted about, and also what Steve Jones has rightly pointed out...to minimise the risk of a backup device being corrupt.
I'm not sure how to automatically detect if a backup device is corrupt. I haven't had the misfortune of having that happen, so I haven't been fortunate enough to learn from that :). Can I assume the next backup operation to that device/file (like the next Differential or Log backup) would fail, and that would give an opportunity for some form of notification? Or do I have to literally perform a restore to somewhere for each device to confirm the backup is healthy. I'm not sure that is practical, since the device is being written to every hour for the Log backups.
Thanks Again...
Garry
April 14, 2020 at 8:10 am
I'm not sure how to automatically detect if a backup device is corrupt. I haven't had the misfortune of having that happen, so I haven't been fortunate enough to learn from that :). Can I assume the next backup operation to that device/file (like the next Differential or Log backup) would fail, and that would give an opportunity for some form of notification? Or do I have to literally perform a restore to somewhere for each device to confirm the backup is healthy. I'm not sure that is practical, since the device is being written to every hour for the Log backups.
Garry
The only way to be sure a backup is good is to restore it. Obviously it's not practical (and you've just identified another disadvantage of making all your backups to the same file) to restore every backup, but restoring a random sample every now and then should give you a reasonable level of confidence about the integrity of your backups.
John
April 14, 2020 at 4:29 pm
Only a restore, as John noted, tests this. That is only at that point in time, and later the file might get corrupted, especially during copying, but doing this regularly is a good way to build confidence.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply