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
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
RECONFIGURE
EXEC SP_CONFIGURE 'xp_cmdshell', 1
RECONFIGURE
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
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.bak'
--Full
BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
EXEC SP_CONFIGURE 'xp_cmdshell', 0
RECONFIGURE
EXEC SP_CONFIGURE 'show advanced options', 0
RECONFIGURE
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.
Comments