As a DBA we are responsible for managing backups across all the SQL server instances which include production, data warehouse, development and QA with more than 200 SQL server instances in dedicated and shared database environments with more than 500 databases
with different recovery models and DRP requirements.
There are many 3rd party tools available including SQL server native utility, maintenance plans to create the backup jobs. Maintenance plans are very helpful to create and schedule the backups, but they have very limited features where
you can’t customize the backups plan as per your requirement which includes, copy the backups to secondary location, deletes the backups in secondary location, take the backups based on recovery model, keeping a track of which database been has a backup and
monitor the status of all the backup jobs across all the instances in a single dashboard.
Addressing the below problems first involves deciding the right approach to choose maintenance plans or customized SSIS package to control and monitor all the database backups.
Finally, maintenance plans are good to quickly create the regular maintenance jobs which include the backup tasks, but on multi-database environments where the server has more than 10 databases, it’s difficult to monitor, control and maintain the backup
Below framework/solution addresses all the problems listed above. You can modify the same solution as per your requirement to control multiple instances with multiple databases. and you can configure and check whether the backup jobs are configured as per
your DRP requirements.
Backup extension. Don’t enter “.”
Enter 1 to compress the backups
Enter 1 to verify the backup
Enter the backup type Full /Diff/Log
Enter local database backup path.
If you want to take the backups directly to a remote location then enter the remote location name
This is optional. Enter remote location to move the local backups to this location.
This is Optional. Enter the select query to include the list of the backup files to delete.
Eg: below query produce the list of old backups except recent,full +recent differential + all log backups after recent diff backup.
select files_to_delete from vw_keep_recent_full_nd_diff
Enter the select query to list the databases to take the backup.
Eg: below value includes the backups X,Y,Z
select name as dblist from master.sys.databases where name in ('X','Y','Z')
You can filter the database based on your own criteria.
For e.g.: To take the log backups of all databases which are in full recovery model , set the variable value with “select name
as dblist from
where recovery_model_desc='full' “
Get DB List
This retrieves the results from the select query in variable
I_SqlDBList and sets the output to another local variable dblist
Get Next ExecutionID
This retrieves the next ExectutionID value from BKP_file_locations using below statement to set the Execution number during each execution of the job.
The purpose of using ExecutionID column is to know which database backup failed during an execution.
SELECT ISNULL(max(ExecutionID),0)+1 as ExecutionID FROM BKP_file_locations
Loop All DBLIst
Loops through all the databases listed in dblist variable
Creates subdirectories for each database in backup location
Executes the stored procedure [BKP_DBS] to take the backup
Move Bkps To Remote Location
If the optional input variable I_Optional_RemoteBkpPath sets to a remote location path then it moves the backups from the local path to remote path
< Container >
get old file list to delete
If the optional input variable I_Optional_SqlRetention sets to a SQL query to produce the list of the old backup files to delete then it
Sets the local variable retentionFileList with the list of the files resulted from SQL query in I_Optional_SqlRetention
Loop All Old File List
Loops through all the files to delete
Delete old backups
Deletes the old backups one at a time in a loop and set the status of the delete operation in tracking table ( Bkp_file_locations)
This is an event handler for “for loop container” to log the errors in tracking table.
Note: you should review and modify the view vw_keep_recent_full_nd_diff to delete the old backups , I have provided this view for simulation purpose only .
Next: To create multiple jobs , you can script out the existing job and change the variable values
More on deleting old backups:
Attached download files has a view “vw_keep_recent_full_nd_diff” , this view refers the tracking table “bkp_file_locations” and produce the list of the remote old backup files to delete except the recent full ,recent differentials and all log files
after the recent differential backup.
I guess your article is more sutable for SSIS category
>>•When the new database created in a shared database instance then you may miss to include the backup in your backup job.
This is not correct this can be handled by Maintenance plan as well
No. not in all the cases.
if you wan to take the t-log backups of all the databases , but the recovery model of the one of the database is simple then the maintanance plan will fail.
this package address this problem by including all the databases where the recovery model <> simple
select name from sys.databases where recoverymodel_desc <> 'simple'
I am not talking about Tlog backups I was specifically talking about'... ' new database created in a shared database instance then you may miss to include the backup in your backup job....'. In maintenance plan if you are scheduling full backup and and during database selection option if you select all databases ,even if someone creates a database without your knowledge it will be included in backup.