slopfront
Reddit, read-only. No JavaScript. No cookies. No tracking.
preferences

Backup & Recovery of Production SQL DB's in AG's

3 points · submitted Feb 2021 by u/corrigun · MS SQL

Hi All,

I worked for a SMB where we had a few apps that had SQL backends of moderate size for in house use. Say around 500GB to 1 TB total. We backed up these servers in their entirety using Quest Rapid Recovery and frequently tested them by exporting them as new VM's and spinning them up. Also Quest allows you to run "attachability checks". This action checks the consistency of SQL databases and ensures that all supporting MDF (data) and LDF (log) files are available in the backup.

​

I recently moved to a large Enterprise environment that run huge SQL DB's (tens of TB's) in availability groups for dev and production. I understand they back these up fully once a day manually? As in copy/paste and scripts. Is that common practice? It seems to cause them a great deal of headache and frankly seems like an outdated and unmanageable practice.

I feel like most modern backup solutions (Veeam, Altaro, RR, etc) could handle the server and one of the nodes of the group but by my own admission I'm no DBA. What IS common practice to backup and restore large SQL DB's in production?

23 comments · 64% upvoted · other discussions

comments

queenmamadala 2 points · Feb 2021

Shouldn't the question be if these are same server backups? I've had our dedicated server go down, making its automated backups useless.

Whoever's asking for the daily manual backup probably got burned by a same-server automated backup on a system that failed.

Are they having you move the manual backups to an air-gapped machine?

Unless you work for a financial institution or something of that ilk, I'd see if you can reduce the frequency. Otherwise, do it.

corrigun [OP] 2 points · Feb 2021

Many groups across many environments going to different storage. They all suffer the same problems really. Its unmanageable and clunky, jobs step on other jobs, jobs run longer and longer. Jobs have no real way of checking success. One team backing up servers with traditional methods (Altaro, Veeam, etc) and another backing up DBs manually.

Im not doing any of it currently. Its not really in my scope anymore. I'm just watching the mess and wondering why there isn't an easier way.

alinroc 1 points · Feb 2021 · SQL Server DBA

I understand they back these up fully once a day manually? As in copy/paste and scripts. Is that common practice?

If you are backing up your production databases manually (and through no other means), you're doing it wrong.

corrigun [OP] 1 points · Feb 2021

Agreed so what is "right"?

alinroc 5 points · Feb 2021 · SQL Server DBA

It depends on what your storage setup is, what your RPO & RTO is, what your DBAs & sysadmins prefer, and what kind of money you can spend.

My personal preference is for SQL Server native backups via Agent jobs using Ola Hallengren's Maintenance Solution. But I've never had to deal with single databases in the 10+ TB range.

corrigun [OP] 1 points · Feb 2021

Why would you use scheduled tasks and scripts when actual backup software is a thing?

[deleted] 1 points · Feb 2021

it is easy to setup, flexible, highly configurable, matches well (duh) to capabilities of the server, supported by MS support, backward and forward compatible and transferrable to other environments.

A better question is - what is in your 'backup software' that can outweigh all the possible negatives of a third-party approach?

corrigun [OP] -1 points · Feb 2021

So if I understand you correctly, task designed backup and recovery speciality software that comes with 24/7 support is not as desirable as a script from the Internet?

I guess I would say scheduling, queueing, compression, deduplucation, central management, and a slew of proprietary features.

[deleted] 2 points · Feb 2021

Yep. The script produces something that MS support will accept. 'Recovery specialty software' produces a file that is "valid" but does not work. MS support will tell you to shove it. My ultimate goal in production is that my server instance is working, not that my backup solution is working "correctly".

p.s. also if Ola's scripts wont work for whatever reason i can absolutely whip up an equivalent for my specific configuration in 15-20 minutes. You'd be on hold with your 24/7 support for that long and it will take a call to your account rep and another hour to get escalated to Sev 1. I've been there, personally, so no, thank you.

corrigun [OP] 0 points · Feb 2021

Oh boy. Well, thanks for the input.

[deleted] 1 points · Feb 2021

[deleted]

corrigun [OP] 1 points · Feb 2021

It depends on what you use for a schedule. You can backup (and replicate and standby) virtually constantly if the environment can handle it.

You should take a look at some modern backup software or appliances. This is the same vibe I get from people at work.

Try searching for live recovery from Quest for example.

[deleted] 1 points · Feb 2021

This is the way.

corrigun [OP] 0 points · Feb 2021

Its not the way. At least not at any scale. Which is why I posed the question.

[deleted] 2 points · Feb 2021

10s of terabytes isn’t a large environment.

Using Ola’s scripts is perfectly suitable for this.

Backup software like Litespeed can actually makes things more complicated and can hinder RTO.

What makes you think otherwise?

corrigun [OP] -1 points · Feb 2021

Experience.

alinroc 3 points · Feb 2021 · SQL Server DBA

Experience as not a DBA?

corrigun [OP] 0 points · Feb 2021

Experience as someone who managed an entire environment including successful backup and recovery of servers and SQL DBs

[deleted] 1 points · Feb 2021

Successful backup and recovery? As opposed to what exactly? Backup, but unable to recover from the backups?

You’re doing something wrong if you’re struggling with Ola’s scripts with only a 20 TB server.

load 0 more replies →

PossiblePreparation 1 points · Feb 2021

“As in copy/paste”, you mean the DB files are being manually copied at the file system level? That would only ever “work” as a backup if the database isn’t attached... is that the case? Seems pretty silly to be doing that when using Availability Groups. Hopefully I’m misunderstanding you

corrigun [OP] 1 points · Feb 2021

How about we just go with best practice for back up and/or recover SQL DBs in AGs.

PossiblePreparation 1 points · Feb 2021

What do you think that best practise is? And you didn’t answer my question about clarifying what you’re currently doing?

corrigun [OP] 1 points · Feb 2021

Ok thanks, moving on.