#basicbackup
So you’re taking backups! That’s great. Lots of people aren’t, and that’s not so great. Lots of people think they are, but haven’t bothered to see if the jobs are succeeding, or if their backups are even valid. That’s not great, either; it’s about the same as not taking them to begin with. If you want an easy way to check on that, sp_Blitz is your white knight. Once you’ve got it sorted out that you’re taking backups, and they’re not failing, here are some questions you should ask yourself. Or your boss. Or random passerby on the street. They’re usually nice, and quite knowledgeable about backups.
These questions don’t cover more advanced scenarios, like if you’re using Log Shipping or Mirroring, where you have to make sure that your jobs pick up correctly after you fail over, and they certainly don’t address where you should be taking backups from if you’re using Availability Groups.
How often am I taking them?
If someone expects you to only lose a certain amount of data, make sure that your backup schedules closely mimic that expectation. For example, when your boss says “hey, we can’t lose more than 15 minutes of data or we’ll go out of business”, that’s a pretty good sign that you shouldn’t be in simple recovery mode, taking daily full backups. Right? Plot this out with the people who pay the bills.
I’m not knocking daily full backups, but there’s no magic stream-of-database-consciousness that happens with them so that you can get hours of your data back at any point. For that, you’ll need to mix in transaction log backups, and maybe even differentials, if you’re feeling ambitious.
Am I validating my backups?
Most people see their backups completing without error as victory. It’s not bad, but making sure they can be restored is important, too. Sometimes things go wrong!
It’s also helpful for you to practice restore commands. A little bit of familiarity goes a long way, especially when it comes to troubleshooting common errors. Do I need to rename files? Restore to a different path? Change the database name? Do I have permissions to the folder? This is all stuff that can come up when doing an actual restore, and having seen the error messages before is super helpful.
Where am I backing up to?
If you’re backing up to a local drive, how often are you copying the files off somewhere else? I don’t mean to be the bearer of paranoia, but if anything happens to that drive or your server, you lose your backups and your data. And your server. This puts you in the same boat as people not taking backups.
How many copies do I have?
If you lose a backup in one place, does a copy exist for you to fall back on? Are both paths on the same SAN? That could be an issue, now that I think about it.
How long do I keep them?
Do you have data retention policies? How often are you running DBCC CHECKDB? These are important questions. If you have a long retention policy, make sure you’re using backup compression to minimize how much disk space is taken up.
How do I restore them?
Here’s a great question! You have a folder full of Full, Differential, and Log Backups. What do you do to put them together in a usable sequence? Paul Brewer has a great script for that. If you use a 3rd party tool, this is probably taken care of. If you use Maintenance Plans, or Ola’s scripts you might find that a bit more difficult, especially if the server you took the backups on isn’t around. You lose the msdb history, as well as any commands logged via Ola’s scripts. That means building up the history to restore them is kaput.
Way kaput.
Totes kaput.
Thanks for reading!
13 Comments. Leave new
Can you discuss why relying on VSS/Veeam snapshots instead of native SQL backups might be a greek tragedy waiting to happen? I see this a lot in my DBA travels; shop with no DBA, so the systems guys solely rely on some sort of snapshot (pick your flavor) because they don’t have time to set up SQL Server Maint plans (Or Ola’s solution). I think there would be some great value add if advice regarding this came from a source that is, dare I say, more authoritative than myself.
I always use SQL native backups, because I like being employed and being able to have nice things. 🙂
I’m totally with you, when I’m the DBA. When I’m not the DBA, and there is no DBA… Well, native backups might not be the right solution.
There may not be anyone on staff with the ‘DBA strengths’ to deal with native backups, and then restoring them. It can totally be a long-term goal, but it doesn’t happen overnight.
My checklist for backups like you mentioned: not dirty, meet RPO/RTO, being restored successfully and regularly.
Hi Erik,
There’s a @RestoreScriptOnly parameter in the sp_RestoreGene stored procedure, in the SQL Agent jobs that perform backups (FULL, DIFF, LOG), a call to restore gene could be made as a final job step to generate a restore script to an output folder ready for disaster. If the server then crashes and msdb backup history is lost, a restore script is there ready to go. It’s a worse case scenario, no tail of the log, with replace, some lost data but it could be used to restore the databases to the point the backup jobs ran.
Some people are doing this already with restore gene and various suggestions to improve the @RestoreScriptOnly parameter output were made recently. This and a lot of the other recent improvements were prompted by one of Brent’s Monday morning email in June and there’s been a spike in views today from your endorsement here now, thanks.
Best wishes
Paul
Best wishes
Paul
That’s really cool!
Glad you’re getting views, you deserve it. Great work on this!
Here is a plan that is simple and would work for most reasonably-sized situations. We have about 50 databases on an 8-core Dell R430 server (SQL server 2008 R2) with 64GB Ram and 1TB of disk. We do a full backup at Midnight to a second backup SQL server identical in configuration to the production server. The full backups are encrypted and moved off-site at 2am. We then do log backups every 1/2 hour to the backup server. Each night at 10pm the day’s full backup and log backups are restored on the backup server to fully test the process end to end. Full-backups and log backups are kept for 2 weeks. Full Saturday backups alone are kept for 3 months. All of this is automated via a C# program. Any failure from any step of the process generates an email to a gmail account. This is a proven process that works very well and doesn’t load the system in a noticeable way. We can restore a database to any point in time for the past two weeks. Any data loss would be a maximum of 30 minutes which is quite acceptable. Restoring from the backup to the primary SQL server is fast. Switching to the backup SQL server takes about 10-15 minutes if we need to do a mid-day restore.
Thank you Richard, this is very helpful to read about other business backup plan.
Hi Erik,
Thanks for this post. I was wondering if you could go into a little more detail of why restoring from Ola’s scripts on a new server leaves you kaput? I just tried restoring a series of Full, Diff, and Transaction Log backups generated by Ola’s script and simply plugged them into the Recovery Advisor built into SSMS 2016 and it seemed to work out all the details by itself.
I meant that many people rely on scripts that use msdb and/or the CommandLog table to generate restore scripts from, especially if they’re using Ola’s scripts too. It’s hard to write a good script that hits the file system! If the server is gone, you can’t access those. This can be further compounded if you’re also backing up locally, because you won’t even be able to use the GUI (your backups files are gone).
Does that make more sense? I should probably make that last paragraph more clear; that’s what I get for writing late at night!
Thanks for the clarification Erik. I’ve been using Ola’s scripts for a while and love them, but I really struggled to find community resources around restoring them and wasn’t aware most people script this from MSDB. I always figured it’s best to not rely on that in case of disaster.
We use ola’s backup script and have had no problems restoring those backups to a new server. full, log, diff no issue. Could you expand on this worry of yours?
See my above comment!
The first question I ask is “What are the backup and disaster recovery requirements for your business?” Do the processes above match your requirements for the business. Also, a good question to ask is “Do backups need to be encrypted and how are the backup files secured?”
[…] https://www.brentozar.com/archive/2016/10/questions-asking-backups/ […]