SQL Server database backup automation using T-SQL

I have gone through a request as a part of daily maintenance. Found a backup maintenance plan has been failed due to low disk space.

 “SQL Task     Description: Executing the query “BACKUP DATABASE CA1011_DB TO DISK = …” failed with the following error: “A nonrecoverable I/O error occurred on file “C:\Backup\ CA1011_DB \ CA1011_DB _backup_2013_06_05_010000_7196421.bak:” 112(There is not enough space on the disk.).  BACKUP DATABASE is terminating abnormally.”

 We have designed a maintenance plan that performs backup for all selected databases, once after backup a task added to cleanup the backup files. But the problem is while performing backup all previous day backups are still available on folders and those can be deleted only after backup job completed.

 So for all databases if the total backup size is 200 GB we exactly need 400GB free space on drive. To avoid these issues we have created a T-SQL script which performs backup and clean up the old backup.

 Existing Maintenance Plan:

 Step1: Perform backup for Database-1

New T-SQL Script:

SQL Server database backup automation using T-SQL

 Step1: Perform backup for Database-1

Step2: Deletes previous day backup for Database-1

Step3: Perform backup for Database-2

Step4: Deletes previous day backup for Database-2

—————————————————-

—————————————————-

StepN: Perform backup for Database-N

StepN+1: Deletes previous day backup for Database-N

 Below is the script to perform a backup and deletes previous days backup.

USE MASTER;
GO

IF EXISTS(SELECT 1 FROM SYS.SYSOBJECTS WHERE NAME = 'usp_Backup_Database' AND TYPE='P')
BEGIN
	DROP PROCEDURE [usp_Backup_Database];
END
GO
CREATE PROCEDURE usp_Backup_Database (
				@db_nameNVARCHAR(50),
				@file_pathNVARCHAR(256))
AS
BEGIN
SET NOCOUNT ON
DECLARE	@fileName	NVARCHAR(256);-- Filename for backup
DECLARE	@fileDate	VARCHAR(20);-- Used for file name
DECLARE	@DeleteDate	DATETIME = GETDATE();-- Cutoff date

-- Get date to include with the file name.
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);

-- Build the file path and file name.
SET @fileName = @file_path + @db_name + '_' + @fileDate + '.BAK';

-- Backup the database.
BACKUP DATABASE @db_name TO DISK = @fileName WITH INIT;

-- Delay 10 sec
WAITFOR DELAY'000:00:10';

-- Purge old backup files from the disk.
EXEC master.sys.xp_delete_file 0,@file_path,'BAK',@DeleteDate,0;

END
GO

Now execute the stored procedure:

 Parameters:

@db_name: Database name that needs to be backup

@file_path: File path where backup needs to be stored.

 Note: File path format should be “:\…………\Backup\”

 Procedure execution:

USE MASTER;
GO
EXEC usp_Backup_Database 
			@db_name = N'Source',
			@file_path = N'C:\Bkptest\Source\';
GO

This is really helpful when we need to deal with disk space.

Just to demonstrate I have shown an example with one database that same can be loop for all user defined databases using a table variable and while loop.