How do we automate the database backup process in SQL Server without using a maintenance plan

How do we automate the database backup process in SQL Server without using a maintenance plan
How do we automate the database backup process in SQL Server without using a maintenance plan

In this article we will see the easiest and most efficient way to backup all instance databases. We will do this using the procedure DatabaseBackup which has been made by Ola Hallengren.

Of course, this work is also done with the use T-SQL (e.g. backup database) ή maintenance plans which SQL Server has built-in, but the above has some limitations.

What more does it offer?

The procedure DatabaseBackup it can perform separate backups in user or system databases, delete old backup files with the same job and many more with the various parameters it accepts.

The installation

Download from here the package with the procedures we will need:

or directly from the creator's site here.

After it is downloaded, we execute the entire script in the database that we want to call from it, for example in a custom database that we have made for monitoring or in the systemic master database.

Then to start the process, we simply execute the procedure with the parameters as below:

EXECUTE dbo.DatabaseBackup
@Databases = 'ALL_DATABASES',
--@Directory = 'C:\backup',  --comment for default backup folder
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 360

What exactly does each parameter define:

  • @Databases we define whether the process will run on all databases, the systemic ones or the ones we have made.
  • @Directory we define this specific parameter when we don't want the backup to take to the default directory but to another one.
  • @BackupType we define if it will get FULL / DIFFERENTIAL / LOG backup.
  • @Verify we define whether to verify the backup.
  • @Compress define whether the backup will be compressed.
  • @CheckSum we define whether to enable CheckSum.
  • @CleanupTime we define after how many hours the backups will be deleted.

There are detailed instructions for each parameter on the creator's website here.

How do I automate the process?

We can add the execution of the procedure to a SQL Server Agent Job.

To do this we go to Objects ExplorerSQL Server Agent, right click on JobsNew Job

In the window that appears, go to the tab Steps and we choose New.

There we choose the master database where we had installed the procedure and add the script of the procedure:

How do we automate the database backup process in SQL Server without using a maintenance plan
01

In the tab Schedules we can define the frequency that the task will run. It would be good to choose a time that does not have a heavy workload with access to the data in the instance:

How do we automate the database backup process in SQL Server without using a maintenance plan
02

To see in detail each action that the procedure has done and when it did it

We simply make a select in the commandlog table:

select * from master..commandlog;

Bonus

With the following script that I have made, with just one click, it installs it MaintenanceSolution by Ola Hallengren along with ready configured jobs for Backup ,Database Integrity and Index Optimize with optimal parameters and timing.

Backup jobs are created disabled as we may have another solution for backup with a 3rd party tool. It also checks for Availability Group so that they run only if it is the Preferred Backup Replica (for backup / database integrity) and if it is Primary Replica (for Index Optimize).

All we need to do is run the following script on each SQL Server Instance:

Sources:

Share it

Leave a reply