Introduction

Regular backups of SQL Server databases are essential to prevent data loss. SQL Server Agent can run backup operations automatically on a schedule.

Prerequisites

  • SQL Server Agent is enabled.
  • Appropriate access to SQL Server is available.
  • There is sufficient storage space for the backup.
  • The SQL Server Service Account can access the destination path.

01 — Check SQL Server Agent

In SQL Server Management Studio, confirm that the SQL Server Agent service is in the Running state before creating the job.

02 — Test a full backup

Run a full backup once before scheduling it to confirm that the database and destination path are ready.

T-SQL 
BACKUP DATABASE [NewShop]
TO DISK = N'D:\SQLBackup\NewShop_FULL.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10;

Backup options

COMPRESSION reduces backup size. CHECKSUM helps detect possible corruption during the backup operation, and STATS = 10 reports progress at 10-percent intervals.

03 — Create a SQL Server Agent job

In SQL Server Management Studio, open SQL Server Agent, then Jobs, and select New Job.

On the Steps page, create a step with the Transact-SQL script (T-SQL) type and add the backup command to that step.

04 — Create a dated backup file

Use the following script in the job step to add the execution date to the backup file name.

T-SQL 
DECLARE @FileName nvarchar(500);

SET @FileName =
    N'D:\SQLBackup\NewShop_FULL_' +
    CONVERT(char(8), GETDATE(), 112) +
    N'.bak';

BACKUP DATABASE [NewShop]
TO DISK = @FileName
WITH COMPRESSION, CHECKSUM, STATS = 10;

05 — Configure the schedule

On the Schedules page of the job, add a recurring schedule. For example, configure the Full Backup to run every night at 02:00.

Plan the schedule

Choose a time that fits the workload and available storage capacity of the server.

06 — Verify the backup

Use RESTORE VERIFYONLY to check that the backup set is complete and readable.

T-SQL 
RESTORE VERIFYONLY
FROM DISK = N'D:\SQLBackup\NewShop_FULL_20260902.bak';

Important limitation

RESTORE VERIFYONLY is not a replacement for a real restore test. Backups must be restored periodically to a test environment and verified there.

07 — Review job history

In SQL Server Management Studio, expand SQL Server Agent and Jobs, right-click the job, and select View History. Review failed runs and the messages recorded for the job step; use the SQL Server Agent error information to investigate failures.

Troubleshooting

SQL Server Agent is not running

Start SQL Server Agent and confirm that its status is Running before expecting the schedule to execute.

The destination path cannot be accessed

Verify that the backup folder exists and that the SQL Server Service Account has the required access to it.

There is not enough disk space

Check the available space in the backup destination and review the retention plan before the next scheduled run.

The job fails or does not run

Review Job History, the job step message, the schedule configuration, and SQL Server Agent error information to identify the failed stage.

Best practices

  • Keep the backup destination available and ensure the SQL Server Service Account has the required access.
  • Use COMPRESSION and CHECKSUM as appropriate for the backup policy.
  • Review Job History after scheduled runs and investigate failed executions promptly.
  • Periodically restore backups to a test environment; verification alone is not a restore test.

Frequently asked questions

What type of job step should be created for the backup command?

Create a step with the Transact-SQL script (T-SQL) type and place the backup script in that step.

Can RESTORE VERIFYONLY replace a test restore?

No. It verifies that the backup is complete and readable but does not perform a real restore. Periodically restore the backup to a test environment.

Where can I review failures of a scheduled backup job?

Use View History for the job in SQL Server Agent and inspect the job-step messages and SQL Server Agent error information.

Conclusion

A scheduled SQL Server Agent job automates full backups after the database, destination path, permissions, and schedule are configured. Verify every backup and regularly perform a real restore in a test environment.

Official Microsoft Learn resources

Share