Automate/Scheduled Database Backup SQL Server 2012 Express | SSIS | T-SQL

Here we will create and ETL which will keep full database backup of last 7 days. And will remove the older backup file/folder with are older than 7 days.
This we will do by SSIS and we will use T-sql script for take backup.
This T-Sql Script will take backup of all your database excluding ('master','model','msdb','tempdb') databases.
I think that you know SSIS, So lets create an ETL(dtsx file)/SSIS project
Drag and drop following 7 containers and connect with your server.

1). Check Database Integrity
(Check the allocation and structural integrity of user and system tables, and indexes in the database)
2.) Shrink Database Task
(It reduces the size of SQL Server database data and log files)
3). Reorganize Index
(Reorganizes indexes in SQL Server database tables and views)
4). Rebuild Index
(Rebuilds indexes in SQL Server database tables and views)
5). History Cleanup
(History Cleanup task, a package can delete historical data related to backup and restore activities, SQL Server Agent jobs, and database maintenance plans.)
6). Take the full backups and store it in E:\Vikas Ahlawat\CURRNET PROJECTS\DB Backup SSIS\DB_BackupPlan\DB_Backup_Daily\<yyyymmdd>\
Following is the T-Sql backup script you can use
 EXEC SP_CONFIGURE 'show advanced options', 1  
 EXEC SP_CONFIGURE 'xp_cmdshell', 1  
 DECLARE @folderdate VARCHAR (50) --The subdir for my backups with Format YYYYMMDD  
 DECLARE @cmd VARCHAR (4000) --The command to create Subdir  
 DECLARE @name VARCHAR(50) -- database name   
 DECLARE @path VARCHAR(256) -- path for backup files   
 DECLARE @fileName VARCHAR(256) -- filename for backup   
 SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112)   
 SET @path = 'C:\Backup\' + @folderdate + '\'  
 SELECT @cmd = 'md "'+@path+'"'  
 EXEC master..xp_cmdshell @cmd, no_output  
 DECLARE db_cursor CURSOR FOR   
 SELECT name   
 FROM master.dbo.sysdatabases   
 WHERE name NOT IN ('master','model','msdb','tempdb')  
 OPEN db_cursor    
 FETCH NEXT FROM db_cursor INTO @name    
     SET @fileName = @path + @name + '.bak'   
     FETCH NEXT FROM db_cursor INTO @name    
 CLOSE db_cursor    
 DEALLOCATE db_cursor   
 EXEC SP_CONFIGURE 'xp_cmdshell', 0  
 EXEC SP_CONFIGURE 'show advanced options', 0  
7). Delete the backup files which are older than 7 days. (Powershell Script)
For this you can user Powershell Script, to create it use notepad file and write the following code and save as .ps1 file.

$Today = Get-Date

$DaysToKeep = "7"

$TargetFolder = "E:\Vikas Ahlawat\CURRNET PROJECTS\DB Backup SSIS\DB_BackupPlan\DB_Backup_Daily"

$LastWrite = $Today.AddDays(-$DaysToKeep)


$Folders = Get-ChildItem -path $TargetFolder |

Where {$_.psIsContainer -eq $true} |

Where {$_.LastWriteTime -le "$LastWrite"}


foreach ($Folder in $Folders)


    if ($Folder -eq $null) {

        write-host "No folders to delete" -foregroundcolor "Green"


    else {

        write-host "Deleting "$Folder.FullName -foregroundcolor "Red"

        Remove-Item $Folder.FullName -recurse -Confirm:$false



After create this you can use this script in SSIS by using Execute process task

It will automate your backup process. So you don't need to take backup manually or delete older backup file. All thing is done by above ETL. You can schedule it as a Job in Sql server Agent. Which will run on a particular time everyday.

Post a Comment

* Please Don't Spam Here. All the Comments are Reviewed by Admin.
  1. This post is very useful for us. Because we have a lot of
    tips and tricks from this post. Thank you for this amazing post share. I many
    tips about bd jobs as well. If you want to know more about a career sites, please visit our website.