Post reply

DB Backups are not getting deleted automatically in SQL SERVER 2005

  • February 24, 2009 at 11:16 pm

    #129252

    Hi All,

    I have scheduled a maintenance plan.In that maintenance Plan, the first step i have selected is to delete the backups which are older than 2 days and then in the second step i m taking the backups of all the databases.

    But now my issue was the job is running perfectly fine taking the backups but it is not deleting the previous backups.When i have checked the T-SQL of the Clean up Backup step is representing to delete the backups corresponding to exact the date and time.

    Since the time stamp of the backups are different they are not getting deleted.

    Can anybody help me to overcome this Issue .Thanks In Advance

  • February 25, 2009 at 1:01 am

    #949825

    What sql server Service Pack are you using?

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • February 25, 2009 at 1:45 am

    #949830

    I am using SP2

  • February 25, 2009 at 3:09 am

    #949853

    free_mascot (2/25/2009)

    "Thare are only 10 types of people in the world:

    Those who understand binary, and those who don't."

    😀 You made my day!

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • February 25, 2009 at 4:49 am

    #949871

    😀 You made my day!

    Paul you hit the bull eye buddy 😉

    I am using SP2

    You need to add "Maintenance Cleanup Task" with desire retention period of Database Bakup.

    HTH

    Cheers

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • February 25, 2009 at 9:12 pm

    #950398

    Hi,i have used the Maintenance clean up task as my first task and also mentioned delete the backups whose age is older than 2 days.Even then it is not working for me...

  • February 25, 2009 at 9:43 pm

    #950405

    You have to type in .bak or whatever you're suffixing them (the files) to get SQL Server to delete. It doesn't default to this...

  • February 25, 2009 at 9:50 pm

    #950409

    Iam using RTM version and in that there no need for ".BAK".

    Not sure about SP2.

    "Keep Trying"

  • February 25, 2009 at 9:58 pm

    #950412

    It's probably a service pack. I remember both specifying the file type and the sp for ...maintenance plans for initial SQL 2005 release were terrible and didn't work at all. AT ALL. Get the latest sp (Sp3) and then let us know.

    If that doesn't work pm me and get me a login and i'll do it for you via RDP 😀

  • August 13, 2009 at 12:02 am

    #1038475

    I am getting the same problem even after installing SP3 on the server. Its workign fine on other servers, I have checked all the parameters and it seems to be fine. Kinldy suggest.



    brijesh

  • August 13, 2009 at 11:03 am

    #1038856

    If your maintenance job is not deleting the files then just add and del command using sqlcmdtask add the below code.

    xp_cmdshell 'del c:\.......\*.bak'

    This will automatically delete the .bak contents from the folder.

    In case you just want to delete a specific like a backup taken 2 days ago then generate file name using t-sql.

    Good luck.

  • August 13, 2009 at 12:24 pm

    #1038911

    here is a vbs file can delete files older than 3 days.

    replace the default folder with your folder.

    Dim fso, f, f1, fc

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set f = fso.GetFolder("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\")

    Set fc = f.Files

    For Each f1 in fc

    If DateDiff("d", f1.DateLastModified, Now) > 3 Then f1.Delete

    Next

  • August 14, 2009 at 9:02 am

    #1039433

    I use a Windows 2003 O/S EXECUTABLE called "forfiles.exe".

    I put this command in a BATCH file and schedule it with SQL. This command deletes ALL the ".bak" files from the previous day.

    FORFILES -p D:\PROGRA~1\MICROS~1\MSSQL.1\MSSQL\Backup\MyDatabase\ /M *.bak /d -1 -c "CMD /C DEL @FILE"

  • August 14, 2009 at 9:11 am

    #1039442

    Does your SQL account have access to delete files in backup folder?

  • August 14, 2009 at 9:19 am

    #1039450

    Yes.

    Also, all SQL Server Services are assigned a Domain Wide Account that also belongs to the Servers LOCAL ADMINISTRATORS GROUP.

Viewing 15 posts - 1 through 15 (of 15 total)

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