This article has the objective to present an architecture of how to implement an "Automated Backup Routine", in a simple an objective way, the aim is to simplify the understanding of all its components and their operation.

Let´s pass for three important topics:

• The Maintenance Solution
• A Query for Each Type of Backup
• The Policy Chosen for Our Solution
• Automating Everything on the SQL Agent

The Maintenance Solution

As much as this is an introductory topic — this is exactly where the gold of this article is (I'm being honest)

It was created by olahallengren, the guy simply wrote a query designed to automate all possible maintenance routines

• MaintenanceSolution.sql

The solution was made to attend those borign tasks that we made on day-to-day as a DBA, do you know?

We can say that the solution focus on:

• Database Backup
• Index Optimize
• Database Integrity Check

What do you need to do? Dowload the script and run the solution on your SQL Server

I won't add the Script code below because it contains 9104 lines, but you can download it by clicking here

After run, you can explore the solutions to start you DB implementation. Here is the link for each one:

• DatabaseBackup: SQL Server Backup
• DatabaseIntegrityCheck: SQL Server Integrity Check
• IndexOptimize: SQL Server Index and Statistics Maintenance

In this article I will show you how you can automate — much more — your Backup routine!

A Query for Each Type of Backup

First of all, do you know what types of backup exist?

Well, we have FULL, DIFF and LOG. These are the main ones.

In a brief explanation of each one, we can explain that:

• FULL — It is a complete copy of all data in the database at the time the backup is performed.
• DIFF — Captures only changes made to the database since the last FULL Backup.
• LOG — Captures all transactions that have occurred in the database since the last Backup LOG. (If you want to recover at a specific time.)

Now that we understand Backups, let's move on to the solution's Queries. The initial solution provides a lot of parameters for you to add to the Query.

I use the most important ones (for this solution) in this article

I will add the Queries for each type of Backup and a brief explanation of each parameter in the code block:

FULL

-- Beginning of the week
EXECUTE dbo.DatabaseBackup
@Databases = 'your_db', -- Your DB (USER_DATABASES backup all databases)
@Directory = 'C:\Backup', -- Your directory
@BackupType = 'FULL', -- Backup Type (FULL/DIFF/LOG)
@Compress = 'Y', -- Perform compression (Reduz o tamanho do arquivo)
@CheckSum = 'Y', -- Check if everything went well (Nothing was corrupted)
@CleanupMode = 'BEFORE_BACKUP' -- Delete old Backups to avoid filling up storage

DIFF

-- End of each day
EXECUTE dbo.DatabaseBackup
@Databases = 'your_db', -- Your DB (USER_DATABASES backup all databases)
@Directory = 'C:\Backup', -- Your directory
@BackupType = 'DIFF', -- Backup Type (FULL/DIFF/LOG)
@ChangeBackupType = 'Y', -- If there is an error, change the Backup type (Security issue)
@CleanupMode = 'BEFORE_BACKUP' -- Delete old Backups to avoid filling up storage

LOG

-- Each hour
EXECUTE dbo.DatabaseBackup
@Databases = 'your_db', -- Your DB (USER_DATABASES backup all databases)
@Directory = 'C:\Backup', -- Your directory
@BackupType = 'LOG', -- Backup Type (FULL/DIFF/LOG)
@LogSizeSinceLastLogBackup = 1024, -- Specify a minimum size (MB) for the amount of log generated since the last log backup.
@CleanupMode = 'BEFORE_BACKUP' -- Delete old Backups to avoid filling up storage

I added the Queries in a "simple" way, as it is just an example. However, this solution addresses much more complex models. As:

• @URL — You can add the URL of an Azure Blob Storage
• @MirrorDirectory — Want to add more than one repository?
• @Encrypt — Encrypt? We have!
• @AvailabilityGroupFileName — Want to customize the name? It is also possible

Anyway, there are an infinite number of parameters! And look, there are still two more solutions, we are only talking about Backup here.

The Policy Chosen for Our Solution

Well, I'm going to adapt it to the reality of a database that is updated in intensive use, not necessarily in batch processing.

Batch processing is a data processing approach in which a large volume of information is processed in a single run, rather than in real time or in an interactive manner. What is the reality many, no?

For batch processing, I recommend using the same logic below. However, using only FULL and DIFF

Let's get started, I call this the Backup Policy!

The DBA can create a Backup policy merging FULL, DIFF and LOG — and who is the DBA? You my friend!

And the policy chosen was:

• Start of the week (Sunday) — FULL
• End of each day (Except Sunday) — DIFF
• Hourly (Except 12AM and 01AM) — LOG

Cycle ends and we perform a new FULL.

Automating Everything on the Agent

Now that you have the queries, the policy and the logic. Just add everything in SQL Agent and schedule.

Let's do an example together?

1. Create a new Job and let's standardize the names. I like db name + backup type. It would look like this: your_db_FU

2. Go to the Steps Tab and click on New. Copy and paste the Query and make sure the database selected is the master (master!… master!)

3. Go to the Schedules Tab and click New. This is where we will schedule the Agent to run our Query on Sundays. Do the same as the example below:

Redo the same steps for the other Queries, to help you I will add how the scheduling of each one need to be:

Schedule DIFF Backup DIFF needs to run every day except Sunday. Sunday already has an owner!

Schedule LOG Note that here I added the start after 1 am and the end at 11 pm. This way, it does not have to conflict with other Backups

You will find them like this in the folder:

Now just let it run and breathe a sigh of relief, if a DELETE without WHERE happens to your base you already have somewhere to run!

Thanks and see ya!

Doing What Needs to Be Done.