First, let me say that yes, I’m aware of Ola Hallengren’s excellent set of maintenance scripts, including those for doing backups. Truly awesome stuff – but not exactly what I wanted in a backup solution. What I’ve built is not meant to improve upon, replace, or in any way make a statement about Ola’s scripts. This is my solution that I built to address my specific needs. I’m sharing it with the three people who read my blog, just in case they might find it useful.

My team is responsible for dozens of SQL Server instances. Some of them we’ve built, some we’ve inherited, some just appeared one day out of thin air. We try to standardize our builds, but for those servers that just fall into our laps, it’s hard to do. As a result, there’s a lot of inconsistencies in how maintenance tasks are done. Some backup jobs use stored procedures. Some of them issue BACKUP commands directly from the job step. Some use maintenance plans, which makes me ill.

I wanted, OK, needed, a way to quickly configure backups on a server, something that would cover 99% of our backup needs with little or no configuration. I needed a “set it and forget it” solution. Specifically, some of the things I wanted were:

• something generic that could be dropped onto a new server, starting backups immediately
• something flexible enough to cover 99% of our needs, but also handle the exceptions
• something maintenance-free
• something robust enough to automatically fix simple problems, like a missing directory
• something that uses native SQL features so that I can enhance it over time

EXECUTE dbo.sp_BackupDatabases
 @backup_type = 'D',    -- Do full backups only
 @full_backup_frequency = 'W'; -- Do a full backup each week (starting on Sunday)
EXECUTE dbo.sp_BackupDatabases
 @backup_type = 'I',    -- Do differential backups only
 @diff_backup_frequency = 'D'; -- Do a differential backup each day

EXECUTE dbo.sp_BackupDatabases
 @backup_type = 'L';    -- Do log backups only

The post One-stop Shopping for Backups appeared first on RealSQLGuy.