Append differential backup

  • zi


    Points: 2387

    Dear Experts

    look at this scenario

    1- I have created a backup device

    2- I have created a maintenance plan full backup and run it - with overwrite option on the backup device

    3- I have created a maintenance plan differential backup and run it with append option on the same file of backup device

    4- I have created a maintenance plan log backup with append option on the same backup device

    when i made restore database from the backup device i found three files the full and differential and log backup

    5- I ran again the differential backup maintenance plan ( suppose to be ran everyday night )

    when i made restore database I found only two backups the full and the last differential !!!

    what I want to do is to take a full backup every week , append differential backup everyday and append log backup every hour

    when I ran the last differential backup it erased the first differential and log backups, why is this happening and how to apply this scenario and keep the all differential backups on the same backup device

    Thanks lot

  • sqlnyc


    Points: 1726

    your task will be much simpler if you keep backups in separate files. I would also suggest using Ola Hallengren's backup scripts instead of maintenance plans, which can have some really terrible defaults.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    1 backup per file, with the datetime as part of the file name, and don't use backup devices.

    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
  • This was removed by the editor as SPAM

  • Johan Bijnens

    SSC Guru

    Points: 134280

    I don't use maintenance plans because of different issues.

    I use Backup devices for all sqlserver backups ( 3 devices per database ( Full / Diff / Log ) )

    Diff and log backups are incremental.

    I haven't had many issues using backup devices. I've been using them in this setup since SQL2000.

    Down side is msdb will always report the same filename used with a db backup of a given type.

    In any case, your recovery scripts/software will have to examine backup files for their content and see if it matches the database and the needed sequences.


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

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

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