SQL Server Performance

remove backup of logfiles only after a succesfull full backup

Discussion in 'General DBA Questions' started by veldba, Aug 9, 2009.

  1. veldba New Member

    I'm doing a full backup of the database every night and a backup of the logfiles every 15 minutes via a maintenance plan. Full backups are removed after 5 days, transaction backups after 1 day.
    I think a better solution should be that the transaction log backups are only deleted after a successful full backup. When a full backup fails I want to keep my transaction log backups until the next successful full backup.
    How can I automate this?
    Thanks in advance.
  2. preethi Member

    Welcome to the forum!
    This is something I do, you can think of a similar method.
    1. We need to have a clear folder structure to keep the backups. I keep this way: On top, I have Backups folder, under that I have one folder for each database and each in turn will contain 3 folders: Full, Differential and Log.
    2. Add the Full backup job The first step is to take backup. The next step is to remove backups older than 5 days and the 3rd & 4th steps are to remove the differential backup and transaction log backups.
    3. For step1, on failure action select "Quit the job reporting failure" and on success action select "go to the next step"
    4. For step 2 & 3 select "go to the next section" for both outcomes.
    5. I do a similar job for differential backups too.
    Just to note, you can use activeX script to remove the files. Here is a sample
    Dim filesystem, folder, files, file, dbfolder
    dbfolder = "X:BackupMydatabaseFull"
    Set filesystem= CreateObject("Scripting.FileSystemObject")
    Set folder = filesystem.GetFolder(dbfolder)
    Set files = folder.files
    For Each file in files
    If file.DateCreated < (Date - 5) Then
    file.Delete
    End If
    Next
    set filesystem = nothing
    Hope this helps.
  3. veldba New Member

    Thank you very much. Just what I needed!

Share This Page