• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3453
  • Last Modified:

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.

Thanks,

Gabicus
0
Richard Comito
Asked:
Richard Comito
2 Solutions
 
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.
0
 
bradleys40Commented:
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 pagehttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
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.
0
 
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.

Gabicus
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now