June 12, 2017 at 8:06 am
I've been told that one of the SQL boxes I started to admin has daily FULL backups. It has one important database, of about 100GB and the database is in FULL recovery model.
When I suggested changing this strategy from daily FULL, to weekly FULL+DIFFs (we already have Tlog backups) I was told the reason for daily FULL is to avoid the problem with corrupted backups and to simplify restore process.
Any opinion about this? I personally believe that we only way to attack backup corruption is via restore and DBCC checks...
June 12, 2017 at 8:23 am
Yes, I agree with you, although I will say that if your databases are of a size that allows a full backup every night, then I would go for that option, since it does indeed simplify the restore process, as well as reducing the risk of someone or something taking some action that breaks the backup chain.
John
June 12, 2017 at 9:12 am
I agree with John, if you've got the capacity to run nightly full backups then I would. It makes life easier if you ever have to come to restoring as you can skip the 'DIFF' and just restore last night's full.
https://sqlundercover.wordpress.com/
June 12, 2017 at 9:22 am
Appreciate the input.
I may still suggest noon's DIFFs, to reduce the amount of Tlogs needed during a backup. Tlogs are currently taken every 5 min.
June 12, 2017 at 9:34 am
Yes, although if you have a script to generate your log restore statements that won't be too much of an issue unless you have a fairly large and highly transactional database. Just make sure you keep the number of VLFs under control in order to minimise recovery time. And, of course, test. As you're aware, this is the only way you're going to know for sure whether you can get your database back in a time that's acceptable to the business.
John
June 13, 2017 at 1:41 am
Test the restore process regularly. That's the best thing to do for any backup. I also like the idea of adding a mid-day diff. Test that too though. And test the logs every so often. Any of these things can fail. Not that they will fail, but they could.
"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
June 13, 2017 at 2:19 am
there are plenty of scripts online that can help automate your restore process. e.g.:
https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/
Also, as has already been said, if you can take a full backup each day then do it.
But as you correctly pointed out, test a restore with DBCC CHECKDB... it's all well and good taking a 'shortcut' to "avoid the problem with corrupted backups", but if you aren't testing your backups, how do you know they aren't already corrupted?
June 13, 2017 at 4:38 am
daily fulls can consume huge amounts of space if the database is large.
Daily diffs are usually a lot smaller although there may be cases where the diffs are huge too.
Generally though you'll save space overall across the server\estate
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 13, 2017 at 6:38 am
Perry Whittle - Tuesday, June 13, 2017 4:38 AMdaily fulls can consume huge amounts of space if the database is large.
Daily diffs are usually a lot smaller although there may be cases where the diffs are huge too.
Generally though you'll save space overall across the server\estate
True. But as per IT, space is not the issue (at this moment)
June 13, 2017 at 9:27 am
TheSQL_fan - Tuesday, June 13, 2017 6:38 AMPerry Whittle - Tuesday, June 13, 2017 4:38 AMdaily fulls can consume huge amounts of space if the database is large.
Daily diffs are usually a lot smaller although there may be cases where the diffs are huge too.
Generally though you'll save space overall across the server\estateTrue. But as per IT, space is not the issue (at this moment)
think about it before it does become an issue, it's no use waiting til it does become an issue then frantically try to fix it.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 13, 2017 at 9:43 am
Grant Fritchey - Tuesday, June 13, 2017 1:41 AMTest the restore process regularly. That's the best thing to do for any backup. I also like the idea of adding a mid-day diff. Test that too though. And test the logs every so often. Any of these things can fail. Not that they will fail, but they could.
Yes, all this.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply