October 2, 2009 at 7:16 am
What are the advantages of 'Append to existing backup set' in the Back Up Database for SS 2005?
Why would you want to continue to append to an existing backup media set rather than ALWAYS overwriting the existing one?
I have read the BOL and this isn't outlined, only what it does.
Thanks,
Zee
Accidental DBA (bear w. me)
October 2, 2009 at 7:28 am
Fred Zimmerman (10/2/2009)
What are the advantages of 'Append to existing backup set' in the Back Up Database for SS 2005?Why would you want to continue to append to an existing backup media set rather than ALWAYS overwriting the existing one?
I have read the BOL and this isn't outlined, only what it does.
Thanks,
Zee
Accidental DBA (bear w. me)
well it appends by default, it does keep all your backups in one file, so have a history of backups in 1 media set to return to.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 2, 2009 at 7:32 am
backing up to tape might be useful so you can re-use tape.
backing up to disk no advantage at all.
If using the maintenance plan backup to file, it will suffix a date stamp for you and you specify how many days worth to keep.
---------------------------------------------------------------------
October 2, 2009 at 7:34 am
As near as I can tell, it's a personal preference.
Your backup scripts are easier if they they always write to the same location. You don't have to come up with some naming scheme to show that a backup is from yesterday as opposed to the day before. You always know where to go to get your backup, so restores are easier to automate as well.
However, you can't look at a backup file and know what's inside it. You have to run scripts against it to get the list of available backups. I find that to be a huge pain. Your backup files will also be larger. If you keep 3 days worth of backups available, they're all in that one file, which makes it harder to transfer to other people or other servers.
I'm not aware of a single point that says one method is better than the other. It's just different ways of managing the process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 2, 2009 at 7:36 am
What are the advantages of 'Append to existing backup set' in the Back Up Database for SS 2005?
On other hand the big disadvantage of this option is a problem to move/copy one single backup file of the backup set to another place.
October 5, 2009 at 5:40 am
Let's say I have 3 separate [days] backups in one (appended) backup file.
Can I stipulate, later, that I want to restore any one of those days as a point in time to do restore from?
Thanks,
Zee (Atlanta)
General Dynamics SS DBA
October 5, 2009 at 6:28 am
ZeeAtl (10/5/2009)
Let's say I have 3 separate [days] backups in one (appended) backup file.Can I stipulate, later, that I want to restore any one of those days as a point in time to do restore from?
Thanks,
Zee (Atlanta)
General Dynamics SS DBA
Yes, absolutely. You don't lose any functionality in regards to backup or restore by using a single file with multiple backups. You just need to make sure you use the proper syntax to tell the server which of the backups you're using. You can name them or refer to them by their ordinal position.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2009 at 6:53 am
There isn't any (big) advantage I can see to appending to disk. However there are disadvantages. As Grant mentioned, you can't easily see what's inside. But more so there's a risk. If something happens to that one file, if there's a bad block, if you have any issue with the media, you've lost multiple backups. If that's your last 3 days of backups, then you are losing 4 days worth of work.
don't do it. Use separate files each day.
October 5, 2009 at 8:20 am
Grant,
Can you give me an example to illustrate, or point me to an exact BOL example (I looked and could not find this in BOL).
I have a backup named WSS_Content_OFL_Protection-Full_DB_Backup.
I've backed up Monday Oct. 5
Wednesday Oct. 7
and Friday Oct. 9
On Monday Oct. 11 I want to Restore Wed. Oct. 7 point in time DB.
Thanks,
Zee
October 5, 2009 at 9:36 am
First, you want to gather information about the backups that exist. The best way to do this is to get the header information:
RESTORE HEADERONLY
FROM DISK = 'c:\data\restoretest.bak';
That will give you the names and/or numbers associated with the backups. Then when you restore, you include, in the WITH clause the FILE = statement kind of like this:
RESTORE DATABASE x
FROM FILE = 'c:\mybackup'
WITH REPLACE, FILE = 2;
In this case, it'll restore the the second backup file (they're one based as far as I know) in the backup set.
There are more details available in books online in the basic RESTORE statement page and on a page titled "Media sets, media families, and backup sets."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply