Implementing a Scheduled Automatic SQL Server Backup Process

Now that we have reviewed the backup basics in the first article of our series dedicated to SQL Server backup, let’s proceed to the implementation and automation of the SQL Server backup process.

First, let me provide this article that will help implement SQL Server auto-backup on one server and make a consequent restore on a different one using the default MS SQL Server tools.

SQL Server Backup Types

The previous article in the series delivered a thorough overview of the SQL Server backup basics. In this paragraph, let’s briefly mention that Microsoft SQL Server allows three main types of backup:

1. Full backup.
2. Differential backup.
3. Transaction log backup.

As for the first one, a full backup is a complete copy of the objects in the database, and it serves as a foundation for other backup types. By implementing the full backup, you can easily recover the database and all of its contents as well as part of the transaction log.

Next, a differential backup is a backup that contains all the changes that have been made since the most recent full backup. Its size differs depending on the amount of modified data and the number of changes that have been introduced to a database. This type of backup is most applicable when there have been a few recent transactions made. It allows saving time and performing a quick backup.

Finally, a transaction log backup is the type of backup that deals with backing up transaction logs, which contain the whole history of data modifications. If you need to recover a database to a certain point in time, this type of backup is exactly what you need.

Initial Steps of SQL Server Backup Implementation

To begin with, we need to create the SRV schema in the necessary database unless created before. After that, we create the [srv].[BackupSettings] table to include the databases to be backed up:

CREATE TABLE [srv].[BackupSettings](
	[DBID] [int] NOT NULL,
	[FullPathBackup] [nvarchar](255) NOT NULL,
	[DiffPathBackup] [nvarchar](255) NULL,
	[LogPathBackup] [nvarchar](255) NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BackupSettings] PRIMARY KEY CLUSTERED 
(
	[DBID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

ALTER TABLE [srv].[BackupSettings] ADD  CONSTRAINT [DF_BackupSettings_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

Let’s clarify: 

1. DBID is the database ID.
2. FullPathBackup is the full path to the backup location.
3. DiffPathBackup is the full path to the differential backup location.
4. LogPathBackup is the full path to the transaction log backup location.
5. InsertUTCDate is the date and time of making an entry in UTC.

Following that, we add our JobEmplDB database into the [srv].[BackupSettings] table that we created. To do that, use the below script:

INSERT INTO [srv].[BackupSettings]
           ([DBID]
           ,[FullPathBackup]
           ,[DiffPathBackup]
           ,[LogPathBackup])
     SELECT
           DB_ID('JobEmplDB'),
		   N'\\Shared\Backup\Full\',
		   N'\\Shared\Backup\Diff\',
		   N'\\Shared\Backup\Log\';
GO

Implementing Full SQL Server Backup

According to the information provided above, we can see that the [srv].[RunFullBackupDB] stored procedure for making a full backup has been defined as shown here. Check the example of the [srv].[RunFullBackupDB] stored procedure call with all the parameters:

EXEC [srv].[RunFullBackupDB]
@ClearLog=1,
@IsExtName=1,
@IsCHECKDB=0,
@IsContinueAfterError=0,
@IsCopyOnly=0,
@OnlyDBName=NULL,
@IsAllDB=0,
@FullPathBackup=N'\\Shared\Backup\Full\';

Hence, the current stored procedure defines the following input parameters:

1. @ClearLog identifies the necessity to truncate the transaction logs after the backup process is finished (1 – yes, other values – no). 
2. @IsExtName identifies that the backup name has to include the date and time in YYYY_MM_DD_HH_MM_SS, UTC format (you need to add it).
3. @IsCHECKDB specifies whether the database has to be checked e before backup.
4. @IsContinueAfterError defines if it should continue the backup process if errors occur (1 – yes, 0 – no, NULL – the default value).
5. @IsCopyOnly specifies if it should make a copy-only backup (1 – yes, 0 and NULL – no).
6. @OnlyDBName defines the need to make a backup for the database specified only (if this parameter is enabled, and @IsAllDB=1, it searches for the necessary database among all the non-system databases).
7. @IsAllDB defines whether it should process all databases except for the system ones (1 – yes, NULL – no).
8. @FullPathBackup specifies the full path to the catalog for backup making (is active if @IsAllDB=1).

After the execution of the script provided above, we can receive the following backup copy file: JobEmplDB_Full_backup_2020_07_19_11_23_55.bak

The file name indicates that this is a full backup copy of the JobEmplDB database created at 11:23:55 UTC on 19 October 2020.

Implementing Differential SQL Server Backup

Based on the information presented above, we get the full definition of the [srv].[RunDiffBackupDB] stored procedure for making full backups, which is described as follows here.
An example of the stored procedure [srv].[RunDiffBackupDB] call with all parameters:

EXEC [srv].[RunDiffBackupDB]
@ClearLog=1,
@IsExtName=1,
@IsCHECKDB=0,
@IsContinueAfterError=NULL,
@OnlyDBName=NULL,
@IsAllDB=0,
@DiffPathBackup=N'\\Shared\Backup\Diff\';

This stored procedure defines the following input parameters:

1. @ClearLog indicates the necessity to truncate the transaction logs after the backup process completion (1 – yes, other values – no).
2. @IsExtName specifies the necessity to add the date and time in YYYY_MM_DD_HH_MM_SS, UTC format to the backup name.
3. @IsCHECKDB indicates whether it should check the database before backup.
4. @IsContinueAfterError determines whether it should continue the backup process if errors occur (1 – yes, 0 – no, NULL – the default value).
5. @OnlyDBName indicates the necessity to make a backup for the database stated only (if the parameter is present, and @IsAllDB=1, it will search for the database among the non-system databases).
6. @IsAllDB indicates whether it should process all the databases, except for the system ones (1 – yes, NULL – no).
7. @DiffPathBackup is the full path to the backup directory (takes place if @IsAllDB=1).

After the execution of the script provided above, we get the following backup copy file: JobEmplDB_Diff_backup_2020_07_19_11_41_53.bak

The backup name states that this is a differential backup of the JobEmplDB database, created at 11:41:53 UTC on 19 July 2020.

Implementing Backup of Transaction Logs

Based on the information exposed above, we get the full definition of the [srv].[RunLogBackupDB] stored procedure for making full backups, which is described as follows here.
An example of the stored [srv].[RunLogBackupDB] procedure call with all parameters is as follows:

EXEC [srv].[RunLogBackupDB]
@ClearLog=1,
@IsExtName=1,
@IsCHECKDB=0,
@IsContinueAfterError=NULL,
@IsCopyOnly=0,
@OnlyDBName=NULL;;

The current stored procedure defines the following input parameters: 

1. @ClearLog identifies the necessity of truncating transaction logs after the backup process completion (1 – yes, other values – no).
2. @IsExtName specifies the necessity of adding a timestamp in YYYY_MM_DD_HH_MM_SS, UTC format to the backup name.
3. @IsCHECKDB identifies whether the database is being checked before making the backup.
4. @IsContinueAfterError specifies whether it should continue the backup process if errors occur (1 – yes, 0 – no, NULL – the default value).
5. @IsCopyOnly indicates whether it should make a copy-only backup (1 – yes, 0 and NULL – no)
6. @OnlyDBName indicates to backup only the specified database.

After the execution of the script provided above, we get the backup file: JobEmplDB_Log_backup_2020_07_19_15_00_38.trn

The backup name indicates that this is a transaction log backup of the JobEmplDB database made at 15:00:38 UTC on 19 July 2020.

To sum up, we have created three stored procedures, which allow performing the following backup types:

1. Full.
2. Differential.
3. Backup of transactions logs.

Scheduling SQL Server Backup

Now, you can invoke these stored procedures according to a particular schedule via the agent tasks or any other third-party tools to achieve the automatic SQL Server backup.

Additionally, you can parallelize backup creation via the dynamic creation of Agent tasks with the [inf].[RunAsyncExecute] stored procedure, which has the four following parameters defined:

1. @sql is the T-SQL code for execution within the Agent task.
2. @jobname is the name of the Agent’s task, to which you add GUID (optional).
3. @database is the name of the database, within which the query specified in the @sql parameter should be executed.
4. @owner specifies the login, on behalf of which the Agent task will be executed (unless specified, it will use sa).

As a rule, we apply the two following approaches to create backups:

1. The first approach is suitable for small databases:

2. The second approach is suitable for large databases:

To automate your backup process and ensure your data safety, you can use dbForge Studio for SQL Server, which allows you to fully control and customize your backup process.

Conclusion

To put it briefly, in the article, we exemplified and explained the ways of how to create the stored procedures to implement the main backup types supported by SQL Server, learned how to invoke these stored procedures, and provided you with the best practices of how to schedule and automate SQL Server backup. Further on, we are going to deal with the automation of the database restore from the backup copy. Keep up to date to learn more!