Append vs. Overwrite

I have a maintenance plan set up for my data base to back up on a nightly basis.  I have it set to if file exists to append the file.  I was always under the impression that appending the file just meant to change the data that has changed and to keep a record of past backups.  But today I took a look at my backup file to see that it was over 100gigs.  The Database that I am backing up is only 9 gigs.

What I am trying to do is to always have a couple days of backups on hand so in the unforeseen case that something happens when I am doing a backup.  However, I do not have room to hold a 100 gigs worth of backup data.  Should I be just doing an overwrite of the backup file if exists?  If so what is the best way to make sure that I always have a couple days worth of Data.


Richard ComitoDirector of ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob SiddellCommunity Educator IICommented:
what you were trying to do is an incremental or differential backup. To append something means to add it on so it's just tacking the backup onto the end of the other backup making a very large file. So you might try an incremental or differntial backup instead of appending or having multiple backup files or backup locations to get several days.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
the append and overwrite is what to physically do with the backup, it is the recovery model that decides whether the backup is full or differential, there is log file backups to consider also.
I you are responsible for this you need to understand the differences between these and choose the correct one for you organisation

for 2000 see this web page
Note: For information on Backup and Restore in SQL Server 2005, see Backing Up and Restoring Databases in SQL Server in SQL Server 2005 Books Online.
for 2000
Note: For information on Backup and Restore in SQL Server 2005, see Backing Up and Restoring Databases in SQL Server in SQL Server 2005 Books Online.
Richard ComitoDirector of ITAuthor Commented:
sorlus & bradleys40,

Thanks for your replies.  I am going to split the points.  After pulling out the old trusty SQL Server 2005 Administrators Companion.  I have decided to create 2 Plans that will do a full backup of both the DB and Log files.  One will run on the opposite days of the other.  That way I will always have two days worth of backups.  From what I read about differential backups is that I cannot do a restore with that alone.  So I do need to have a full backup.

Thanks again for your help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.