How do we automate the database backup process in SQL Server without using a maintenance plan
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
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 Explorer, SQL Server Agent, right click on Jobs, New 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:
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:
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:
