Delete database backup history in SQL Server by serverku

As I have written the article to clean up the old database backup for the maintenance activities. The same thing i am doing here as a maintenance activity, but not for the clean up of database backups, It is for database backups history cleanup from MSDB database. Please read my earlier posts related database backup, you may like them.

1. Database Backup Statistics and History
2. Archive old database backup files using forfiles.exe and FOR /F
3. Archive old database backup files using TSQL Script

It is required to clean up the database backup history periodically if no longer needed. Because if the msdb database has more history then it will take the time to load it or sometime it will fail to load because of time out. The script to clean up the database backups, history and a demo are as follows, First we will look for the history of all the database backups done by this SQL instance.

USE msdb 
GO

SELECT bs.database_name, 
       bs.backup_start_date, 
       CASE bs.type 
         WHEN 'D' THEN 'Full Backup' 
         WHEN 'I' THEN 'Diff Backup' 
         WHEN 'L' THEN 'Log Backup' 
       END AS BackupType, 
       bmf.physical_device_name 
FROM   msdb..backupset bs 
       INNER JOIN msdb..backupmediafamily bmf 
               ON ( bs.media_set_id = bmf.media_set_id ) 
ORDER  BY bs.backup_start_date DESC 

We have two methods to delete the database backup history.

#1. Based on DateTime By passing the date in stored procedure , SP will delete all the database history older then specified date time. Let us run below query and get the output.

DECLARE @BackupOlderdate DATETIME
SET @BackupOlderdate = '2011-05-17 14:22:13.000'
EXEC sp_delete_backuphistory @BackupOlderdate
GO

#2. Based on Database
By passing the Database name in stored procedure, SP will delete all the database history for specified database only. Let us run below query and get the output.

DECLARE @DBname sysname
SET @DBname = 'trialmaster'
EXEC sp_delete_database_backuphistory @DBname
GO

Please comment here if you know any other methods to delete it.

Archive old database backup files using forfiles.exe and FOR /F by serverku

Before so many posts published I posted to the old database backup files archive using TSQL and using SSIS. Hope you visit that post and you liked them. I am repeating the same thing here, but it will be with two different methods, the first one with FOR /F command which we saw last time to copy database backups to another location too, second one with forfiles.exe. Let us move on the first method.

FOR /F :  As we discussed earlier post, it used same to traverse rows from generated file from SQLCMD command. Here we do not have values with delims (,) and we have only one values to grab and assign in variables.

SQLCMD -Udba -Pdba@1234 -S"PARESH\MSSQLSERVER2012" -dmsdb -Q"set nocount on ; 
SELECT DISTINCT bmf.Physical_device_name FROM  msdb.dbo.backupset (nolock) bs 
INNER JOIN msdb.dbo.backupmediafamily (nolock) bmf on (bs.media_set_id = bmf.media_set_id) 
WHERE bs.backup_finish_date < DATEADD(Day,-7,GETDATE()) " -o "C:\BackupFile.txt"

FOR /F "tokens=1,1 skip=2 delims=," %%G IN (C:\BackupFile.txt) DO del %%G

Please make sure SQLCMD and FOR /F should be in single line individualized. Running above code and captured snapshot as follows,

forfiles.exe : It is used to delete files in giving directories and subdirectories with a specified day or date. Syntax of it is following, Here /P is a directory, /m for search criteria, /S for subdirectories search, /d for days or date and /C command to fire. Please visit this site for more detail.

forfiles [/p <Path>] [/m <SearchMask>] [/s] [/c "<Command>"] [/d [{+|-}][{<Date>|<Days>}]]

Let us run it and capture snap again with the method,

Hope you have some other methods to archive old database backups. Request to share here!

Archive files based on number of files configuration in SQL Server - SSIS by serverku

Hope you read earlier posts to archive files which are older as per days and you liked them. If you have not read then you can go there by following where you have various scenarios with the same,

1. Archive old database backup files using TSQL Script - SQL Server
2. Delete files from specified folder using File System Task in SQL Server - SSIS
3. Delete files from specified folder using Script Task in SQL Server - SSIS
4. Delete files older than retention period from specified folder using Script Task in SQL Server - SSIS
5. Delete files older than retention period from folder with parameter using Script Task in SQL Server - SSIS

Recently I was asked by my friends to delete the files bases on number of files, not based on days. Such a way we can keep number of files every time and rest files get archived. I will give you an example here as I have three folders and want to keep the different number of files in each folder only. Says keep 3 files in BackupFolder1, keep 2 files in BackupFolder2 and keep 1 file in BackupFolder3. So how can we achieve it? Let's move on the steps,

First we will check the files existing in folders,

We are creating a SSIS package here and will use tasks to perform the archive process,

Also the variables used in the whole task flow,

1.
You can see the flow of the tasks, so we will be here for task 1 which will have fetch the records from a table which really need to be archived. We have inserted folder paths and a number of the files which need to keep in each folder. Let's view the records in a table,

Now we will create a task1 with Execute SQL Tasks and get the list of archive folders as figured below,

It fetches the folders and archive numbers for each row and assign to a variable which is object data type. For variable assignment you need to set a ResultSet option to 'Full result set' as above. After that, you mapping variables are as following for output,

2.
We need to go through the values coming out from variable and then map it to two variables again for archive folder name and archive numbers using Foreach Loop Container,

You can see how the object variable holds the values and assigned to other variables again as per below shot,

3.
Now use Execute SQL Task again and get the list of the files which need to deleted, You need to write some SQL statement to get those files as per archive numbers. So we will look the configuration and variable value mapping and using them in the script. You need to enable xp_cmdshell from the configuration.

Same as earlier steps we need to put the SQL query in SQL Statement option and set 'Full result set' for ResultSet, You can see two arrows which will be input values of the script which going to be executed, You can collect the same script here,

CREATE TABLE #TempFiles
(
Seq int identity(1,1),
FileName varchar (500)
)

DECLARE @FullPath varchar(500), @sql varchar(500)

SET @FullPath = ?
SET @sql = 'dir /B /O-D '+ @FullPath

INSERT INTO #TempFiles (FileName)
EXEC xp_cmdshell @sql

SELECT @FullPath + FileName  as FullName
FROM #TempFiles
WHERE FileName is not null
AND Seq > ?

DROP TABLE #TempFiles

After completion of above to get the resulted values in another variable and pass them to the next step,

4.
Use Foreach Loop Container to process, delete operation for each files same steps we did earlier for Foreach Loop,

5.
Finally, use the Script Task to delete the file one by one as I did in below image. You need to pass the value of the file name as read only,

Now use the following script to perform actual tasks for deletion, Use below script in your script task,

You can copy same script from here,

--//Apply below line in #region namespaces
using System.IO;

--//Apply below code inside 
public void Main()
{
string FilePath = Dts.Variables["User::FileName"].Value.ToString();
System.IO.File.Delete(FilePath);
Dts.TaskResult = (int)ScriptResults.Success;
}

After running the package files will be deleted as per archive number defined in the tables for each folder.

You can schedule this package in some frequency. Hope this help you.