Post reply

Delete DB after "x" Days

  • January 23, 2015 at 8:42 am

    #397920

    Hello,

    I am creating a Scheduled JOb that will delete backups older than 3 days old, however I seem to be missing something in my script. Can someone help me understand what I am doing wrong?

    DECLARE @DeleteDate datetime

    SET @DeleteDate = DateAdd(day, -3, GetDate())

    EXECUTE master.dbo.xp_delete_file 0,N'E:\BackUps\',N'bak',N' + @DeleteDate + ''

  • January 23, 2015 at 8:43 am

    #1772672

    Why do you think something's missing? Is it giving an error? Not working? Something else?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2015 at 8:49 am

    #1772681

    Hi,

    Thank you for you response. I get the following error message:

    Msg 22049, Level 15, State 0, Line 0

    Error executing extended stored procedure: Invalid Parameter

    So it seems my syntax is off. Also I realized by original post is missing a single quote. Here is the correct code:

    DECLARE @DeleteDate datetime

    SET @DeleteDate = DateAdd(day, -3, GetDate())

    EXECUTE master.dbo.xp_delete_file 0,N'E:\Travel_Baks\',N'bak',N' + @DeleteDate + '''

  • January 23, 2015 at 8:58 am

    #1772690

    It looks like you're trying to pass in the date parameter as a character string. I don't think that's necessary. Have you tried doing this?

    DECLARE @DeleteDate datetime

    SET @DeleteDate = DateAdd(day, -3, GetDate())

    EXECUTE master.dbo.xp_delete_file 0,N'E:\Travel_Baks\',N'bak', @DeleteDate

  • January 23, 2015 at 10:00 am

    #1772728

    thank you, that was it!

  • January 23, 2015 at 10:53 am

    #1772755

    🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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