Multiple backup job schedules for one database to different devices

I want to know if it is possible to create backups on differnet
schedules of the same database? And if not, if there some solution
that can fit what I am trying to do?
I want to backup more aggressively to a locally attached disk, and less
aggressively to a network device. Specifically a virtual device in SQL
server using NetBackup SQL Connectors. For example, can we have a
backup schedule that creates a dull backup of a database everyday, and
have TLog backups every 30 minutes both to local disk. But we also
want to have a seperate schedule for the network device that does a
weekly fully, and say hourly differentials or TLog backups thru the
week. Would the checkpointing done by the BACKUP commands somehow
clobber one another?
It would be like:
Monday:
3:00AM - Full Backup -> Local Disk (daily)
3:30AM - TLog Backup -> Local Disk
4:00AM - Full Backup -> Network Disk (weekly)
4:00AM - TLog Backup -> Local Disk
4:30AM - TLog Backup -> Local Disk
5:00AM - TLog Backup -> Network Disk
5:00AM - TLog Backup -> Local Disk
5:30AM - TLog Backup -> Local Disk
6:00AM - TLog Backup -> Network Disk
6:00AM - TLog Backup -> Local Disk
....
Tuesday:
3:00AM - Full Backup -> Local Disk (daily)
3:30AM - TLog Backup -> Local Disk
4:00AM - TLog Backup -> Network Disk
4:00AM - TLog Backup -> Local Disk
4:30AM - TLog Backup -> Local Disk
5:00AM - TLog Backup -> Network Disk
...
I don't think this will work, but could it? And if not, would a
differential backup work for the Network Disk? The goal is to minimize
bandwidth because we can't backup several hundred gigabytes every
morning to a remote site. But we need near live backups incase we had
a total meltdown at the other site.
Regards,
--
Mike BrancatoHi Mike
You probably don't want to do this, as it would make finding the necessary
files when rolling forward the transaction log more difficult. You may want
to consider the alternative of always backing up to local disc and then
selectively copy to the network drive (all log backups) when required.
To get uneven intervals you can schedule your backup jobs to have multiple
schedules one for each period, although this would not overcome the change of
backup device or backup type.
John
"mike.brancato@.acs-inc.com" wrote:
> I want to know if it is possible to create backups on differnet
> schedules of the same database? And if not, if there some solution
> that can fit what I am trying to do?
> I want to backup more aggressively to a locally attached disk, and less
> aggressively to a network device. Specifically a virtual device in SQL
> server using NetBackup SQL Connectors. For example, can we have a
> backup schedule that creates a dull backup of a database everyday, and
> have TLog backups every 30 minutes both to local disk. But we also
> want to have a seperate schedule for the network device that does a
> weekly fully, and say hourly differentials or TLog backups thru the
> week. Would the checkpointing done by the BACKUP commands somehow
> clobber one another?
> It would be like:
> Monday:
> 3:00AM - Full Backup -> Local Disk (daily)
> 3:30AM - TLog Backup -> Local Disk
> 4:00AM - Full Backup -> Network Disk (weekly)
> 4:00AM - TLog Backup -> Local Disk
> 4:30AM - TLog Backup -> Local Disk
> 5:00AM - TLog Backup -> Network Disk
> 5:00AM - TLog Backup -> Local Disk
> 5:30AM - TLog Backup -> Local Disk
> 6:00AM - TLog Backup -> Network Disk
> 6:00AM - TLog Backup -> Local Disk
> .....
> Tuesday:
> 3:00AM - Full Backup -> Local Disk (daily)
> 3:30AM - TLog Backup -> Local Disk
> 4:00AM - TLog Backup -> Network Disk
> 4:00AM - TLog Backup -> Local Disk
> 4:30AM - TLog Backup -> Local Disk
> 5:00AM - TLog Backup -> Network Disk
> ...
> I don't think this will work, but could it? And if not, would a
> differential backup work for the Network Disk? The goal is to minimize
> bandwidth because we can't backup several hundred gigabytes every
> morning to a remote site. But we need near live backups incase we had
> a total meltdown at the other site.
> Regards,
> --
> Mike Brancato
>|||The problem with copying the local files is that the SQL backup process
may take a while and hold a lock on the large databse files. also, the
local backups are several hundred gigs every day, but we would like to
backup remotely only Tlogs, etc thru the week becasue we cannot handle
that much bacndwidth from our servers every day.
Is there any way of doing this?
John Bell wrote:
> Hi Mike
> You probably don't want to do this, as it would make finding the necessary
> files when rolling forward the transaction log more difficult. You may want
> to consider the alternative of always backing up to local disc and then
> selectively copy to the network drive (all log backups) when required.
> To get uneven intervals you can schedule your backup jobs to have multiple
> schedules one for each period, although this would not overcome the change of
> backup device or backup type.
> John
> "mike.brancato@.acs-inc.com" wrote:
> > I want to know if it is possible to create backups on differnet
> > schedules of the same database? And if not, if there some solution
> > that can fit what I am trying to do?
> >
> > I want to backup more aggressively to a locally attached disk, and less
> > aggressively to a network device. Specifically a virtual device in SQL
> > server using NetBackup SQL Connectors. For example, can we have a
> > backup schedule that creates a dull backup of a database everyday, and
> > have TLog backups every 30 minutes both to local disk. But we also
> > want to have a seperate schedule for the network device that does a
> > weekly fully, and say hourly differentials or TLog backups thru the
> > week. Would the checkpointing done by the BACKUP commands somehow
> > clobber one another?
> >
> > It would be like:
> >
> > Monday:
> > 3:00AM - Full Backup -> Local Disk (daily)
> > 3:30AM - TLog Backup -> Local Disk
> > 4:00AM - Full Backup -> Network Disk (weekly)
> > 4:00AM - TLog Backup -> Local Disk
> > 4:30AM - TLog Backup -> Local Disk
> > 5:00AM - TLog Backup -> Network Disk
> > 5:00AM - TLog Backup -> Local Disk
> > 5:30AM - TLog Backup -> Local Disk
> > 6:00AM - TLog Backup -> Network Disk
> > 6:00AM - TLog Backup -> Local Disk
> > .....
> > Tuesday:
> > 3:00AM - Full Backup -> Local Disk (daily)
> > 3:30AM - TLog Backup -> Local Disk
> > 4:00AM - TLog Backup -> Network Disk
> > 4:00AM - TLog Backup -> Local Disk
> > 4:30AM - TLog Backup -> Local Disk
> > 5:00AM - TLog Backup -> Network Disk
> > ...
> >
> > I don't think this will work, but could it? And if not, would a
> > differential backup work for the Network Disk? The goal is to minimize
> > bandwidth because we can't backup several hundred gigabytes every
> > morning to a remote site. But we need near live backups incase we had
> > a total meltdown at the other site.
> >
> > Regards,
> >
> > --
> > Mike Brancato
> >
> >|||Hi
With multiple jobs and schedules you can do just about anything. If you have
a highspeed dedicated lan between the backup server and your database server
it should not cause too much of a problem. Without trying it you will not
know!
If you have a dedicated backup server with high speed dedicated lan it may
be better to do everything to the backup server rather than the local server!
You database will not be "locked" while you are backing up.
SAN technologies have methods that can move large volumes of data instantly,
if you are looking at significant amounts of data it would probably worth
investigating this option.
John
"mike.brancato@.acs-inc.com" wrote:
> The problem with copying the local files is that the SQL backup process
> may take a while and hold a lock on the large databse files. also, the
> local backups are several hundred gigs every day, but we would like to
> backup remotely only Tlogs, etc thru the week becasue we cannot handle
> that much bacndwidth from our servers every day.
> Is there any way of doing this?
> John Bell wrote:
> > Hi Mike
> >
> > You probably don't want to do this, as it would make finding the necessary
> > files when rolling forward the transaction log more difficult. You may want
> > to consider the alternative of always backing up to local disc and then
> > selectively copy to the network drive (all log backups) when required.
> >
> > To get uneven intervals you can schedule your backup jobs to have multiple
> > schedules one for each period, although this would not overcome the change of
> > backup device or backup type.
> >
> > John
> >
> > "mike.brancato@.acs-inc.com" wrote:
> >
> > > I want to know if it is possible to create backups on differnet
> > > schedules of the same database? And if not, if there some solution
> > > that can fit what I am trying to do?
> > >
> > > I want to backup more aggressively to a locally attached disk, and less
> > > aggressively to a network device. Specifically a virtual device in SQL
> > > server using NetBackup SQL Connectors. For example, can we have a
> > > backup schedule that creates a dull backup of a database everyday, and
> > > have TLog backups every 30 minutes both to local disk. But we also
> > > want to have a seperate schedule for the network device that does a
> > > weekly fully, and say hourly differentials or TLog backups thru the
> > > week. Would the checkpointing done by the BACKUP commands somehow
> > > clobber one another?
> > >
> > > It would be like:
> > >
> > > Monday:
> > > 3:00AM - Full Backup -> Local Disk (daily)
> > > 3:30AM - TLog Backup -> Local Disk
> > > 4:00AM - Full Backup -> Network Disk (weekly)
> > > 4:00AM - TLog Backup -> Local Disk
> > > 4:30AM - TLog Backup -> Local Disk
> > > 5:00AM - TLog Backup -> Network Disk
> > > 5:00AM - TLog Backup -> Local Disk
> > > 5:30AM - TLog Backup -> Local Disk
> > > 6:00AM - TLog Backup -> Network Disk
> > > 6:00AM - TLog Backup -> Local Disk
> > > .....
> > > Tuesday:
> > > 3:00AM - Full Backup -> Local Disk (daily)
> > > 3:30AM - TLog Backup -> Local Disk
> > > 4:00AM - TLog Backup -> Network Disk
> > > 4:00AM - TLog Backup -> Local Disk
> > > 4:30AM - TLog Backup -> Local Disk
> > > 5:00AM - TLog Backup -> Network Disk
> > > ...
> > >
> > > I don't think this will work, but could it? And if not, would a
> > > differential backup work for the Network Disk? The goal is to minimize
> > > bandwidth because we can't backup several hundred gigabytes every
> > > morning to a remote site. But we need near live backups incase we had
> > > a total meltdown at the other site.
> > >
> > > Regards,
> > >
> > > --
> > > Mike Brancato
> > >
> > >
>