SQL Schedule Database Backup using SQL Server Agent

Описание

In this video we will skill up how to create a scheduled job to get full backup database using SQL Server Agent in SQL Server management studio.

This tutorial shows step by step process of creating the job using SQL Server Management Studio as well as T-SQL Script in SQL Server.

Store procedure:

USE [master]
GO

DROP PROCEDURE IF EXISTS [dbo].[AccountingDb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AccountingDb]
AS
SET NOCOUNT ON;

-- Declare variables
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBNAME varchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)

-- Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')

-- Create backup filename in pathfilename.extension format for full,diff and log backups
SET @BackupFile = 'D:\Database_Bakcup_SQL2016\Accounting\'+REPLACE(REPLACE('AccountingDb', '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

-- Provide the backup a name for storing in the media
SET @BackupName = REPLACE(REPLACE('AccountingDb','[',''),']','') +' full backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed
SET @sqlCommand = 'BACKUP DATABASE ' +'AccountingDb'+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

-- Execute the generated SQL command
EXEC(@sqlCommand)