How To Create Backup Job Automatically in SQL Server

Introduction

As a good DBA, it is critical to backup your data. However, sometimes, we have the backup, and the backup is corrupt. If that happens to a mission-critical environment, you could be fired because of the error.

This article will talk about the different ways to find out if a SQL Backup file is corrupt, automate the backup process, and some advice to test and avoid this problem.

Getting Started

Let's start with a simple backup.

The syntax for a simple backup is like the following:

BACKUP DATABASE [AdventureWorks2019] TO  DISK = N'C:\backups\AdventureWorks2019.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2019-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
Go

The T-SQL command is doing a Backup of a database named AdventureWorks2019 in the C drive and the backups folder. The name of the file is AdventureWorks2019.

There are several types of backups. Full backups that take a backup of all the data. Differential backups that backups the difference between the full and the current status, Filegroup backups (to backup only specific filegroups), mirror backups (to get copies of the backup).

To get more information about backup types, please refer to the link provided.

Usually, if the backup fails, you will know that you cannot produce a backup. You can see all the backups available inside the file, by pressing the Contents button in the UI in SSMS:

The content will show you the backup type, SQL Server, user name, date, LSN (Log Sequence Number), and more information.

The following query will allow seeing the backups media family available:

select * from msdb.dbo.backupmediafamily

You will get the media set id, family id, and physical device names. For more information about the system backupmediafamily, refer to this link.

If you want to see the backup sets, you can use the following queries available.

select * from msdb.dbo.backupset

The query will provide the backup set id, backup set id, media set id, first family number, and more information related to the backup sets.

For more information about the dbo.backupset, refer to the following link available.

To create a backup job automatically is using the SSMS and right-click on the database and select the Back-Up option.

The is a nice option to Script Action to Job which will create the job automatically.

If you want to see if your backup was executed successfully, you need to check the job history. In your SQL Server job, right-click and select the View History option.

The log file viewer will show if there was a failure or not in the backup and you will find there if, during the backup, it was corrupted.

There are several error messages when the backup is corrupt. The most common error is the SQL Server error 823.

 The error message is like the following:

Error: 823, Severity: 24, State: 2.  2010-03-06 22:41:19.55 spid58 The operating system returned error 38 (Reached the end of the file.) to SQL Server during a read at offset 0x000054a72c0000 in file ‘C:\Program Files\Microsoft SQL Server\ MSSQL\DATA\aw.mdf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe, system-level error condition that threatens database integrity and must be corrected immediately. It is recommended to complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online  

This error message is related to a problem in the hardware or the driver causing problems in the I/O requests.

It is possible, that you will see the error when you use the DBCC CHECKDB command. To check the logical and physical integrity of your Database. The command used with the DBCC CHECKDB is the following:

 DBCC CHECKDB

 The command may or may not display error messages.

You can also check the Event Viewer. The Event Viewer displays a lot of events, errors, information messages from Windows including applications like SQL Server.

The Event Viewer contains the Windows Logs and the Application section to see errors related to SQL Server. You can find information related to a corrupted file there.

There is an option to Filter the current log if there are many messages, it will help you to find just the ones related to your problem.

You can filter per logged time (events in the last 12 hours, last hour, last 24 hours, any time, etc), per level (critical, error, warning, information, and verbose). You can also select a category, keywords and select the user and computer.

Usually, the error message could be similar to this one:

The driver detected a controller error on \Device\Harddisk4\DR4 reported by the Disk source in the Event Log.

You can also check to enable the Trace flag 818 to get more information related to I/O errors. The following command will enable the trace flag 818.

DBCC TRACEON (818,-1);

For more information related to TRACE FLAGS, refer to the following link related.

• DBCC TRACEON - Trace Flags (Transact-SQL)

Finally, you can find more information related to a corrupted SQL Backup file by checking the SQL Error log. You can see the SQL Server Logs in SSMS, the Management>SQL Server Logs

The error log allows us to see and check the errors related to SQL Server. Including the corrupted SQL Server Backup files.

You have options to Search and Filter for specific error messages in order to isolate the errors that you are looking for in the system.

In order to see other errors, please refer to the following link: Common SQL Database Corruption Errors, Causes, and Resolutions

To verify, the backup you can test in a testing server if your backup is working.

We recommend creating a job to backup and then restoring the database in a test environment to make sure, the backup is working fine.

Another way to test is using the VERIFYONLY allows to just verify the backup without restoring it.  

RESTORE VERIFYONLY FROM DISK = C:\backups\AdventureWorks2019.bak
GO

What To Do if the Backup Is Corrupted

One alternative is to cry when the backup is corrupted and look for a new job. However, another option that is smarter is to take the help of SQL recovery software. You can check the review of Microsoft MVP Grant Fritchey's review from here. 

Conclusion

In this article, we verified some system tables to check and verify your database backups in SQL Server. We also learned how to create backups, how to automate backups, and finally, we saw how to check the status of the automatic backups. We saw different possible errors during backup and where to verify and also how to verify them. There are several ways to check the errors like the job history, the event viewer, and the SQL Error log. Finally, we show an alternative solution if the backup is corrupted.