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)
Watch video SQL Schedule Database Backup using SQL Server Agent online without registration, duration hours minute second in high quality. This video was added by user rejawebs 08 October 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 331 once and liked it 3 people.