Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

SQL Server - Archive old database backup files using TSQL Script

Jan 7 2012 12:00AM by Paresh Prajapati   

I experienced into one issues for database backups were failed. And this was due to space issues on disk drive. This disk drive is spacific to allocated for the database backups only.

The space was eaten by this database backups and this drive contains so many old backups. I have manually deleted all files and continue this activity so many days.If you create a maintenance plans then it have option to delete old backups files. But i have the stored procedure for the database backups. So i do not have option to delete old and unused database backup files.

Finally, I have created a script to clean those old backups. I have created one stored procedure in which you need to pass there parameters, One is Backup type as want to delete full, differential or transaction log backups. Second is From days and third one is End day.

Please make sure XP_CMDSHELL is enabled in database instance as thisis require to enable it to delete database backup files to be deleted physically. Here is the query to enable it.


EXEC SP_CONFIGURE 'show advanced options',1
I have already told you as i have created script to delete the old DATABASEPROPERTY backups, please find below SP for the same.
SET ansi_nulls ON 

SET quoted_identifier ON 

CREATE PROCEDURE [dbo].[Deletedbbackupoldfiles] @BackupType        CHAR(1) = 'D' 
,-- 'D'-Full, 'I'-Differential, 'L'-Log backup type 
                                                @StartDayFromToday INT, 
                                                @EndDayFromToday   INT 
      SET nocount ON 

      DECLARE @IsFileExists INT 
      DECLARE @DeletedFile VARCHAR(500) 
      DECLARE @OldFiles VARCHAR(500) 
      DECLARE oldfiles CURSOR fast_forward FOR 
        SELECT bmf.physical_device_name 
        FROM   msdb.dbo.backupset bs 
               INNER JOIN msdb.dbo.backupmediafamily bmf 
                       ON ( bs.media_set_id = bmf.media_set_id ) 
        WHERE  device_type = 2 
               AND type = @BackupType 
               AND backup_start_date < = Getdate() - @StartDayFromToday 
               AND backup_start_date > = Getdate() - @EndDayFromToday 

      -- AND DATEDIFF(DAY,BACKUP_START_DATE,GETDATE()) > BETWEEN @StartDayFromToday and @EndDayFromToday 
      OPEN oldfiles 

      FETCH next FROM oldfiles INTO @OldFiles 

            EXEC Xp_fileexist 
              @IsFileExists output 

            IF @IsFileExists = 1 
                  PRINT 'Deleted File : ' + @DeletedFile 

                  SET @DeletedFile = 'DEL ' + @OldFiles 

                  EXEC Xp_cmdshell 

            FETCH next FROM oldfiles INTO @OldFiles 

      CLOSE oldfiles 

      DEALLOCATE oldfiles 
Finally after creating a stored procedures , it is time to run and clean the old database backups. After running it will remove all the old database backups as per specified parameters. Like we have passed 'D',3 and 10 with SP, So i will delete Full database backups which are older then 3 days ago and 10 days before created.
EXEC [DeleteDBBackupOldFiles] 
  @BackupType  = 'D',
  @StartDayFromToday = 3,
  @EndDayFromToday = 10
Which method you are using for old database backup maintenance? You can read my earlier posts for delete files using File System Task and Script Task in SSIS.

Tags: sql, tsql, sql server, ms sql, ms sql server, t-sql, #SQL Server, #sql, database, sql server general, Maintenance, SQL Scripts, backup, query,

Paresh Prajapati
6 · 23% · 7544
3 Readers Liked this
Paresh Prajapati Liked this on 1/7/2012 7:42:00 AM
Profile · Blog · Facebook · Twitter
Jacob Sebastian Liked this on 1/20/2012 9:03:00 PM
Profile · Blog · Facebook · Twitter
Nirav Liked this on 3/28/2012 12:28:00 AM
Profile · Blog · Facebook · Twitter



  • Hi .. I like the script and i tried on my local machine. but is there anyway where i can apply UNC path and deletes 7 days old T-log backup files?

    commented on Jan 20 2012 1:23PM
    Ankit Shah
    294 · 0% · 147

Your Comment

Sign Up or Login to post a comment.

"SQL Server - Archive old database backup files using TSQL Script" rated 5 out of 5 by 3 readers
SQL Server - Archive old database backup files using TSQL Script , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]