SQL Server database backup automation using T-SQL

robot with computer and stethoscope

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

Step2: Perform backup for Database-2
—————————————————-
—————————————————-
StepN: Perform backup for Database-N
StepN+1: Delete the previous day backup from all folders

 

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.

 

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:

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.

 

Posted in SQL Server DBA | Tagged , , , , , , , | Leave a comment

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz