Delete old Backup files using T-sql script

  • Hi in this case my date format used in backup file getutcdate() format but

    to delete files i am using this script

    Declare @sql varchar(250),@retention tinyint

    set @retention=0

    set @sql='Exec master.dbo.xp_cmdshell ''Del E:\SQL2005\Backup\*' + convert(varchar(15),getdate()-@retention,120) + '*.bak'''

    Exec (@sql)

    i also tried with getutcdate()

    but it gives the following error.

    Could Not Find \\E:\SQL2005\Backup\*2008-04-03*.bak

    why i donot understand.

    can any one give heads up.

  • Do not use mapped drive path, use UNC path.

  • The Maintenance Plans are using the extended stored procedure xp_delete_file to delete files. That can also be used in T-SQL code.

    EXECUTE xp_delete_file 0, 'C:\Backup', 'bak', '2008-04-18T00:00:00'

    I have a backup stored procedure that is using xp_delete_file that you can use if you like.

    http://ola.hallengren.com/sql-server-backup.html

    Ola Hallengren

    http://ola.hallengren.com

  • i didnot find any script or solution from this link you provided.

    sorry

  • Actually i am using UNC Path, however i tried again today but this time it is asking comformation like are you sure y

    for this case what i have to do.

    advise

  • If you just need to delete backup files from inside T-SQL I would use a solution based on xp_delete_file.

    Test the command below. It means that you are deleting all backup files older than the specified date and time in the specified directory.

    EXECUTE xp_delete_file 0, '\\Computer\Share', 'bak', '2008-04-18T00:00:00'

    If this works you could easily build some T-SQL so that it is deleting backup files that are older than a number of days.

    Ola Hallengren

    http://ola.hallengren.com

  • These are all great suggestions. I found this article on this site by Robert Pearl.

    http://www.sqlservercentral.com/articles/Administration/2953/

    He has included a script along with a really great description of our disappointment in this issue (thanks Robert). So he has this script and also talks about maintenance Plans in SP2(a). If you have it already, try using the last choice on the list of Maintenance Plans called "Maintenance Cleanup Task". This is a far cry from what we had in 2000. In our environment we need 1 job to delete .BAK (backups) and another for .TRN (Tranaction Logs). Don't forget about a 3rd for the Backup Log Files (*.txt) and a 4th for the actual backup history entry in MSDB which is a different maintenance plan option called "Clean Up History".

    Try not to have too much fun creating 4 jobs to cleanup after your 1 backup job. And I do mean that in an "Eddy Haskel" kinda way.

    🙂

    Peace.

  • This should work fine for you.Once you create this procedure, just go ahead and create a job and include the step as exec usp_DeleteOldBackupFiles . The code is shown below

    use DB

    go

    if( object_id('usp_DeleteOldBackupFiles') is not null )

    drop PROCEDURE dbo.usp_DeleteOldBackupFiles

    GO

    CREATE PROCEDURE dbo.usp_DeleteOldBackupFiles

    @basedir nvarchar(255),

    @days_old_to_allow int = 30

    AS

    begin

    set nocount on

    declare @mtime datetime

    declare @file nvarchar(255)

    declare @fullpath nvarchar(255)

    declare @daysold int

    declare @cmd nvarchar(255)

    create table #t_dir

    (

    InLine varchar(150)

    )

    -- get a directory listing

    set @cmd = 'dir "' + @basedir + '" /A-D' -- /A-D, no directories

    insert into #t_dir

    exec master.dbo.xp_cmdshell @cmd

    --insert into #t_dir

    -- select * from tmpintable

    delete from #t_dir

    where InLine like ' %'

    or InLine = ''

    or InLine like '% %'

    declare c_files cursor for

    select convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,17) + 'm') as dtime,

    rtrim(substring(InLine, 40, len(InLine))) as filen,

    datediff(dd,

    convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,17) + 'm')

    , getdate()

    ) as daysold

    from #t_dir

    where

    datediff(dd,

    convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,17) + 'm')

    , getdate()

    ) > @days_old_to_allow

    open c_files

    fetch next from c_files into @mtime, @file, @daysold

    while(@@fetch_status = 0)

    begin

    set @fullpath = @basedir + '\' + @file

    print 'Going to delete old file: ' + @fullpath + ', daysold=' + cast(@daysold as nvarchar)

    set @cmd = 'del /Q "' + @fullpath + '"'

    print @cmd

    -- no turning back now!

    exec master.dbo.xp_cmdshell @cmd, no_output

    fetch next from c_files into @mtime, @file, @daysold

    end

    close c_files

    deallocate c_files

    drop table #t_dir

    end

    GO

    /*

    usp_DeleteOldBackupFiles 'c:\mssql\backup', -1

    */

    go

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • Hi,

    maybe you could use xp_delete_file, but this is undocumented.

    Fe EXECUTE master.dbo.xp_delete_file 0,N'C:\Backup',N'BAK',N'2008-08-30T07:49:27',1

    First param : 0= backupfiles, 1 = reportfiles

    2nd param : Path

    3 th param : extension of the files you want to delete

    4th param : all files older than this date will be deleted

    5th param : include subdirs or not

  • Declare @sql varchar(250),@retention tinyint

    set @retention=2

    set @sql='Exec master.dbo.xp_cmdshell ''DIR D:\sql_backup\*'+ substring(convert(varchar(15),getdate(),104),7,4) + substring(convert(varchar(15),getdate(),104),4,2)+substring(convert(varchar(15),getdate()-@retention,104),1,2) +'*.bak /s'''

    print @sql

    Exec (@sql)

  • Declare @sql varchar(250),@retention tinyint

    set @retention=2

    set @sql='Exec master.dbo.xp_cmdshell ''DIR D:\sql_backup\*'+ substring(convert(varchar(15),getdate(),104),7,4) + substring(convert(varchar(15),getdate(),104),4,2)+substring(convert(varchar(15),getdate()-@retention,104),1,2) +'*.bak /s'''

    print @sql

    Exec (@sql)

  • What is the error you are getting?

    Abhijit - http://abhijitmore.wordpress.com

  • If you are using the SQL Agent you can also use VBScript:

    ==========================================================

    Option Explicit

    on error resume next

    Dim oFSO

    Dim sDirectoryPath

    Dim oFolder

    Dim oFileCollection

    Dim oFile

    Dim iDaysOld

    'Customize values here to fit your needs

    iDaysOld = 5

    Set oFSO = CreateObject("Scripting.FileSystemObject")

    sDirectoryPath = "Set backup file path here"

    set oFolder = oFSO.GetFolder(sDirectoryPath)

    set oFileCollection = oFolder.Files

    'Walk through each file in this folder collection.

    For each oFile in oFileCollection

    If oFile.DateLastModified < (Date() - iDaysOld) Then

    oFile.Delete(True)

    End If

    Next

    'Clean up

    Set oFSO = Nothing

    Set oFolder = Nothing

    Set oFileCollection = Nothing

    Set oFile = Nothing

  • First of all Serena's question was how to do it, not why. All answers making a judgment on why and suggesting something else are irrelevant. That being said I have several reasons why I'm moving away from maintenance plans back to T-SQL run from jobs.

    1) My maintenance plans are failing on a regular basis because of poor error handling.

    2) Using maintenance plans requires having SSIS up and running, which is another possible point of failure.

    3) See Ola Hallengren's comparison chart here: http://ola.hallengren.com/MaintenancePlans.html

    4) A Microsoft Premier 3rd level SQL Server support technician agrees with my assessment that maintenance plans do more harm than good.

    Maintenance plans simplify the steps in backing up logs & databases, checking database integrity, shrinking databases, and other mundane chores for a DBA, but that doesn't mean that simple is better. Since the advent of TRY/CATCH error handling in SQL Server 2005 I say that it is better to code these routines and handle the errors in a better manner than just looking at the Job history. I may be opening up myself to flames for taking this position, but I will say that it is better for a DBA to code his own maintenance routines with T-SQL in Jobs than it is to use the Maintenance Plan Wizard.

    I'm open constructive arguments either way. 😛

    Brandon_Forest@sbcglobal.net

  • sreeni .r.julakanti (4/1/2008)


    Guys

    do any one have script to delete old backup files more than 5 days worth using T-SQL Code,if so please post .because i am looking the ways to delete old files in folders.

    Hi

    Check the following link my friend wrote a custom script.

    http://www.sqlservercentral.com/scripts/Administration/68440/

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

Viewing 15 posts - 16 through 30 (of 52 total)

You must be logged in to reply to this topic. Login to reply