Skip to main content
Sign in Menu

SQL Server Agent Jobs

Nathanael Ries
  • Updated

Weekly Full Backups

This SQL Server Agent job, named "Weekly Full Backups," is designed to automate the process of creating full database backups and managing their retention. Here's a breakdown of what it does:

Job Overview

Description:

Performs weekly full backups for all user and system databases (excluding `tempdb`), with backups retained for 1 month.

Notifications:

Logs events to the Windows Event Log on failure or success, but no email, netsend, or pager notifications.

Schedule:

 Runs every Sunday at 2:00 AM (starting from August 26, 2025, with no end date).

Job Steps

The job consists of two sequential T-SQL steps executed in the `master` database. If a step fails, the job stops (with 5 retry attempts at 5-minute intervals for the first step only).

Step 1: Perform Full Backups:

  - Iterates through all databases (excluding `tempdb`) that are online (`state = 0`) and not in standby mode.
  - For each database, creates a full backup file in the network path `\\<server>\<share>\BAK\Full\`.
  - File naming format: `<DatabaseName>_FULL_<YYYYMMDD_HHMMSS>.bak` (e.g., `MyDB_FULL_20251005_020000.bak`).
  - Backup options: Overwrites existing files (`INIT`), uses compression (`COMPRESSION`), and includes a checksum for integrity verification (`CHECKSUM`).

Step 2: Cleanup Old Full Backups

  - Calculates a cutoff date of 1 month prior to the current date.
  - Deletes all `.bak` files in the same network path (`\\<server>\<share>\BAK\Full`) that are older than the cutoff date, including subdirectories (via the extended stored procedure `xp_delete_file`).

Additional Notes

- The job must be added to the local SQL Server instance.
- The script wraps everything in a transaction for atomicity, if any part fails (e.g., due to permissions or errors), it rolls back without creating a partial job.
- This setup ensures regular, automated backups with space management, but relies on proper network access to the backup path and sufficient permissions for the SQL Agent service account.

 

USE [msdb]
GO

/****** Object:  Job [Weekly Full Backups]    Script Date: 9/30/2025 1:55:23 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 9/30/2025 1:55:23 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Weekly Full Backups', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Weekly full backups for all databases (except tempdb), retained for 1 month.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Perform Full Backups]    Script Date: 9/30/2025 1:55:23 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Perform Full Backups', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=5, 
		@retry_interval=5, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @dbName NVARCHAR(128),
        @backupPath NVARCHAR(256) = N''\\<server>\<share>\BAK\Full\'',
        @fileName NVARCHAR(256),
        @fileDate NVARCHAR(20) = FORMAT(GETDATE(), ''yyyyMMdd_HHmmss'');

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN (''tempdb'')
  AND state = 0
  AND is_in_standby = 0;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @fileName = @backupPath + @dbName + ''_FULL_'' + @fileDate + ''.bak'';
    BACKUP DATABASE @dbName TO DISK = @fileName WITH INIT, COMPRESSION, CHECKSUM;
    FETCH NEXT FROM db_cursor INTO @dbName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Cleanup Old Full Backups]    Script Date: 9/30/2025 1:55:23 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cleanup Old Full Backups', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @CutoffDate DATETIME = DATEADD(MONTH, -1, GETDATE());
EXECUTE master.dbo.xp_delete_file 0, N''\\<server>\<share>\BAK\Full'', N''bak'', @CutoffDate, 1;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly Sunday', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20250826, 
		@active_end_date=99991231, 
		@active_start_time=20000, 
		@active_end_time=235959, 
		@schedule_uid=N'1511bfa2-1af0-4f06-9599-bac32dcdc05e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Daily Diff Backups

This SQL Server Agent job, named "Daily Diff Backups," is designed to automate the process of creating Differential database backups and managing their retention. Here's a breakdown of what it does:

Job Overview

Description:

Performs daily diff backups for all user and system databases (excluding `tempdb`), with backups retained for 1 month.

Notifications:

Logs events to the Windows Event Log on failure or success, but no email, netsend, or pager notifications.

Schedule:

 Runs every day at 2:00 AM (starting from August 26, 2025, with no end date).

Job Steps

The job consists of two sequential T-SQL steps executed in the `master` database. If a step fails, the job stops (with 5 retry attempts at 5-minute intervals for the first step only).

Step 1: Perform Diff Backups:

  - Iterates through all databases (excluding `tempdb`) that are online (`state = 0`) and not in standby mode.
  - For each database, creates a diff backup file in the network path `\\<server>\<share>\BAK\Diff\`.
  - File naming format: `<DatabaseName>_DIFF_<YYYYMMDD_HHMMSS>.bak` (e.g., `MyDB_DIFF_20251005_020000.bak`).
  - Backup options: Overwrites existing files (`INIT`), uses compression (`COMPRESSION`), and includes a checksum for integrity verification (`CHECKSUM`).

Step 2: Cleanup Old Full Backups

  - Calculates a cutoff date of 1 week prior to the current date.
  - Deletes all `.bak` files in the same network path (`\\<server>\<share>\BAK\Diff`) that are older than the cutoff date, including subdirectories (via the extended stored procedure `xp_delete_file`).

Additional Notes

- The job must be added to the local SQL Server instance.
- The script wraps everything in a transaction for atomicity, if any part fails (e.g., due to permissions or errors), it rolls back without creating a partial job.
- This setup ensures regular, automated backups with space management, but relies on proper network access to the backup path and sufficient permissions for the SQL Agent service account.

USE [msdb]
GO
/****** Object: Job [Daily Differential Backups] Script Date: 9/30/2025 1:55:23 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 9/30/2025 1:55:23 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Daily Differential Backups',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Daily differential backups for all databases (except tempdb), retained for 1 week.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Perform Differential Backups] Script Date: 9/30/2025 1:55:23 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Perform Differential Backups',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=5,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
DECLARE @dbName NVARCHAR(128),
        @backupPath NVARCHAR(256) = N''\\amznfsx28bpsyan.venio.haug.com\sql\BAK\Diff\'',
        @fileName NVARCHAR(256),
        @fileDate NVARCHAR(20) = FORMAT(GETDATE(), ''yyyyMMdd_HHmmss'');
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN (''tempdb'')
  AND state = 0
  AND is_in_standby = 0;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @fileName = @backupPath + @dbName + ''_DIFF_'' + @fileDate + ''.bak'';
    BACKUP DATABASE @dbName TO DISK = @fileName WITH DIFFERENTIAL, INIT, COMPRESSION, CHECKSUM;
    FETCH NEXT FROM db_cursor INTO @dbName;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Cleanup Old Differential Backups] Script Date: 9/30/2025 1:55:23 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cleanup Old Differential Backups',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
DECLARE @CutoffDate DATETIME = DATEADD(WEEK, -1, GETDATE());
EXECUTE master.dbo.xp_delete_file 0, N''\\amznfsx28bpsyan.venio.haug.com\sql\BAK\Diff'', N''bak'', @CutoffDate, 1;',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20250930,
@active_end_date=99991231,
@active_start_time=20000,
@active_end_time=235959,
@schedule_uid=N'1511bfa2-1af0-4f06-9599-bac32dcdc05e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Related articles

Comments

0 comments

Please sign in to leave a comment.