March 23, 2016 at 8:24 am
Hi all,
I am abit confuse with the terms media set / media family etc ..
If I am to set my backup schedule as
- 1 new media set with 1 backup set ( full database backup) per day on Saturday
- 1 new media set with 1 backup set ( transaction log backup) per day on another other days (Mon to Fri)
e.g.
Sat - mediaset#1 with 1 backupset (full database)
Sun - mediaset#2 with 1 backupset ( transaction log )
Mon - mediaset#3 with 1 backupset ( transaction log )
Tues - mediaset#4 with 1 backupset ( transaction log )
etc..
Am I able to restore the whole database (e.g. Sat to Tues) using different mediaset ?
Regards,
Noob
March 23, 2016 at 10:05 am
The descriptions for those are here. For the most part, most people don't bother with putting backups into a set that way. Instead, most of the time, people will initialize a new media set for each backup, effectively creating a single backup file for each backup. It's the easiest way to manage these things.
The method you outline will work. However, only running a log backup once a day (at least that's how I read it), means that you'll have to have enough drive space to have 24 hours of a log available. Further, you could lose up to 24 hours worth of data. Be sure that the business agrees with that level of Recovery Point Objective.[/url]
"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
March 24, 2016 at 12:31 am
Thank you grant!
March 27, 2016 at 7:19 pm
Hi Grant,
Hope you are still in this thread,
A few days ago, I tried backing up using 2 files/devices.
What does it really means to do a backup with a mediaset containing 2 media family ?
Will the backupset be spread across the 2 media family ? (in which, I will need the 2 files in order to recover my backup)
or
the backupset will be pack sequentially to file#1, then when file#1 fills up, it will move to file#2 ?
Regards,
Noob
March 28, 2016 at 6:48 am
If you set up your backups to go to multiple files, you will need those files for the restore. Using multiple files, on multiple drives, is a mechanism for speeding up backups. However, it complicates recovery because all those files must be available. You can't partially restore from this type of backup. All the files involved are there, or the restore fails.
"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
March 29, 2016 at 10:59 am
Grant Fritchey (3/28/2016)
If you set up your backups to go to multiple files, you will need those files for the restore. Using multiple files, on multiple drives, is a mechanism for speeding up backups. However, it complicates recovery because all those files must be available. You can't partially restore from this type of backup. All the files involved are there, or the restore fails.
Hi grant,
Sorry for the late reply and thank you for your response.
q1) in my case above, if i perform a backup over 2 files, i would not be able to restore the backup using just 1 file right ? (as in, the backupset are not store sequentially on file#1, then file#2, but spread over the 2 files ?
q2) In sql terms above, is each drive consider to be a media family ?
e.g.
a) doing a backup over 2 drives - 2 media family
b) the files, drives as a whole - 1 media set.
c) the individual backup each time - 1 backupset.
Regards,
Noob
March 29, 2016 at 11:34 am
szejiekoh (3/29/2016)
Grant Fritchey (3/28/2016)
If you set up your backups to go to multiple files, you will need those files for the restore. Using multiple files, on multiple drives, is a mechanism for speeding up backups. However, it complicates recovery because all those files must be available. You can't partially restore from this type of backup. All the files involved are there, or the restore fails.Hi grant,
Sorry for the late reply and thank you for your response.
q1) in my case above, if i perform a backup over 2 files, i would not be able to restore the backup using just 1 file right ? (as in, the backupset are not store sequentially on file#1, then file#2, but spread over the 2 files ?
Yes. If that backup spans two files, both files have to be used for the restore. It doesn't fill one, then the other, it writes to both.
[/quote]
q2) In sql terms above, is each drive consider to be a media family ?
e.g.
a) doing a backup over 2 drives - 2 media family
b) the files, drives as a whole - 1 media set.
c) the individual backup each time - 1 backupset.
Regards,
Noob[/quote]
Yeah, I guess. As I said before, I don't tend to think of these things in terms of media sets. I backup to files on drives. That's what gets created, maintained, moved around and restored from.
"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
March 30, 2016 at 1:24 am
Hi Grant,
Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply