I am looking for help in setting up our mirroring / backup / snapshot strategy
We are definately going to use Database Mirroring
I also want to get my databases onto tape for archive. Do I only backup the
Primary SQL server? If so, do I just use the standard MS backup tools? If
so, and I backup the databases to .BAK files on the SAN, then I can just use
Veritas to backup those .BAKs to tape.
What about the Mirror SQL server? It is in a constant state of restore,
right? So how do I back that one up... or do I? If I don't, and it becomes
the Primary, then how is that newly-become Primary going to get backed-up?
Also, how can I fit Snapshots in to my scenario, to allow piecemeal restore
of my data?
thanks so much for any direction
ChrisWhich mirroring option you're planing to use?
Regarding backups of primary, you'll need to set up a strategy for backing
up. I would set up maintenance plans on both servers and then disabled the
one on the Mirror. That leaves the problem of enabling it when it becomes
primary.
I'm not certain what are your requirements so I cant really comment on the
snapshots, only that offcourse you can do them on the mirror server.
MC
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris|||The mirror can NOT be backed up. It is in a restoring state and constantly
appying transactions.
You configure your backups against your principal database. You should also
create the same jobs on the mirror, but disable them. When the mirror fails
over, you enable the jobs and backups continue. You then need to disable
the backups on what used to be the principal. Even though your chain of
backups occurs across 2 instances when you fail it over, this does NOT break
your transaction log chain. This is because the mirror database is
maintained in lock step with the principal, including the LSN chain. (There
are considerations you need to make if you are running in high performance
mode where all of the transactions on the principal might not have made it
to the mirror when it fails over.)
Backups are backups are backups. They don't change, just because you have a
mirror involved.
Database snapshots can be created against a mirror. You can NOT backup a
snapshot nor can you revert from a snapshot unless it is the only one in
existence against the database. It has nothing to do with a piecemeal
restore. A piecemeal restore refers to the ability to restore filegroups to
the database while other portions of the database remain operational and
accessible to applications.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chrissql
Showing posts with label strategy. Show all posts
Showing posts with label strategy. Show all posts
Thursday, March 22, 2012
Thursday, March 8, 2012
database maintenance plan - differential backup
I have implemented a backup strategy using the Full Recovery mode. Using the database maintenance plan I created 2 jobs:
1. Full database backup once each night
2. Transaction Log backups every 15 minutes
However, I would also like to implement a differential backup perhaps 4 times per day. This would save me from having to restore dozens of transaction Log backups in the event of a failure towards the end of a day
I know that the database maintenance plan does not support differential backups. Is there any plans to add this in the future?
Also, if I create a separate job for differential backups, how do avoid the T-Log backup and differential backup executing at the same time without increasing the T-Log interval?
What happens if both the T-Log and differential backup occur at the same time?
Scott,
I basically asked the same question on 07/09 with subject "backup sets to
named device". I solved my own problem like this:
Job A runs once daily at zero hours.
Step 1 creates a backup device pointing to local file with sp_addumpdevice.
Step 2 performs full backup with BACKUP DATABASE.
Step 3 removes old backup devices with sp_dropdevice.
Job B runs daily every 4 hours.
Step 1 performs diff backup with BACKUP DATABASE.
Job C runs daily every 10 minutes.
Step 1 performs log backup with BACKUP DATABASE.
Each job uses datetime and string functions to create the device and file
names, such as NorthWind_YYYYMMDD and NorthWind_db_YYYYMMDD.bak. For step 3
of Job A, I query sysdevices to get the names of old devices. You can
determine how many devices and associated backups remain based on the query.
I end up with complete backups for each day encapsulated in a single file
which gets written to tape and stored offsite.
It's working like a charm. I'll post the detailed t-sql if you need it.
Anthony
|||Scott,
Sorry about not really answering your questions in the previous post. I
have had backup jobs executing at the same time and didn't see any problems.
But for my previous suggestion, I have full at zero hours, logs every 10
minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
04:00 to 23:59. Now none of the jobs intersect.
I haven't looked at Yukon. But since it's .Net based, you will probably
be able to code you're own maint plans.
Anthony
|||Thanks Anthony, I would love to see the T-SQL. It sounds very creative.
My only concern is if both the transaction log and the differential backups execute at the same time, will it cause conflicts somehow.
"Anthony" wrote:
> Scott,
> Sorry about not really answering your questions in the previous post. I
> have had backup jobs executing at the same time and didn't see any problems.
> But for my previous suggestion, I have full at zero hours, logs every 10
> minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
> 04:00 to 23:59. Now none of the jobs intersect.
> I haven't looked at Yukon. But since it's .Net based, you will probably
> be able to code you're own maint plans.
> Anthony
>
>
1. Full database backup once each night
2. Transaction Log backups every 15 minutes
However, I would also like to implement a differential backup perhaps 4 times per day. This would save me from having to restore dozens of transaction Log backups in the event of a failure towards the end of a day
I know that the database maintenance plan does not support differential backups. Is there any plans to add this in the future?
Also, if I create a separate job for differential backups, how do avoid the T-Log backup and differential backup executing at the same time without increasing the T-Log interval?
What happens if both the T-Log and differential backup occur at the same time?
Scott,
I basically asked the same question on 07/09 with subject "backup sets to
named device". I solved my own problem like this:
Job A runs once daily at zero hours.
Step 1 creates a backup device pointing to local file with sp_addumpdevice.
Step 2 performs full backup with BACKUP DATABASE.
Step 3 removes old backup devices with sp_dropdevice.
Job B runs daily every 4 hours.
Step 1 performs diff backup with BACKUP DATABASE.
Job C runs daily every 10 minutes.
Step 1 performs log backup with BACKUP DATABASE.
Each job uses datetime and string functions to create the device and file
names, such as NorthWind_YYYYMMDD and NorthWind_db_YYYYMMDD.bak. For step 3
of Job A, I query sysdevices to get the names of old devices. You can
determine how many devices and associated backups remain based on the query.
I end up with complete backups for each day encapsulated in a single file
which gets written to tape and stored offsite.
It's working like a charm. I'll post the detailed t-sql if you need it.
Anthony
|||Scott,
Sorry about not really answering your questions in the previous post. I
have had backup jobs executing at the same time and didn't see any problems.
But for my previous suggestion, I have full at zero hours, logs every 10
minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
04:00 to 23:59. Now none of the jobs intersect.
I haven't looked at Yukon. But since it's .Net based, you will probably
be able to code you're own maint plans.
Anthony
|||Thanks Anthony, I would love to see the T-SQL. It sounds very creative.
My only concern is if both the transaction log and the differential backups execute at the same time, will it cause conflicts somehow.
"Anthony" wrote:
> Scott,
> Sorry about not really answering your questions in the previous post. I
> have had backup jobs executing at the same time and didn't see any problems.
> But for my previous suggestion, I have full at zero hours, logs every 10
> minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
> 04:00 to 23:59. Now none of the jobs intersect.
> I haven't looked at Yukon. But since it's .Net based, you will probably
> be able to code you're own maint plans.
> Anthony
>
>
database maintenance plan - differential backup
I have implemented a backup strategy using the Full Recovery mode. Using th
e database maintenance plan I created 2 jobs:
1. Full database backup once each night
2. Transaction Log backups every 15 minutes
However, I would also like to implement a differential backup perhaps 4 time
s per day. This would save me from having to restore dozens of transaction
Log backups in the event of a failure towards the end of a day
I know that the database maintenance plan does not support differential back
ups. Is there any plans to add this in the future?
Also, if I create a separate job for differential backups, how do avoid the
T-Log backup and differential backup executing at the same time without incr
easing the T-Log interval?
What happens if both the T-Log and differential backup occur at the same ti
me?Scott,
I basically asked the same question on 07/09 with subject "backup sets to
named device". I solved my own problem like this:
Job A runs once daily at zero hours.
Step 1 creates a backup device pointing to local file with sp_addumpdevice.
Step 2 performs full backup with BACKUP DATABASE.
Step 3 removes old backup devices with sp_dropdevice.
Job B runs daily every 4 hours.
Step 1 performs diff backup with BACKUP DATABASE.
Job C runs daily every 10 minutes.
Step 1 performs log backup with BACKUP DATABASE.
Each job uses datetime and string functions to create the device and file
names, such as NorthWind_YYYYMMDD and NorthWind_db_YYYYMMDD.bak. For step 3
of Job A, I query sysdevices to get the names of old devices. You can
determine how many devices and associated backups remain based on the query.
I end up with complete backups for each day encapsulated in a single file
which gets written to tape and stored offsite.
It's working like a charm. I'll post the detailed t-sql if you need it.
Anthony|||Scott,
Sorry about not really answering your questions in the previous post. I
have had backup jobs executing at the same time and didn't see any problems.
But for my previous suggestion, I have full at zero hours, logs every 10
minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
04:00 to 23:59. Now none of the jobs intersect.
I haven't looked at Yukon. But since it's .Net based, you will probably
be able to code you're own maint plans.
Anthony|||Thanks Anthony, I would love to see the T-SQL. It sounds very creative.
My only concern is if both the transaction log and the differential backups
execute at the same time, will it cause conflicts somehow.
"Anthony" wrote:
> Scott,
> Sorry about not really answering your questions in the previous post. I
> have had backup jobs executing at the same time and didn't see any problem
s.
> But for my previous suggestion, I have full at zero hours, logs every 10
> minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
> 04:00 to 23:59. Now none of the jobs intersect.
> I haven't looked at Yukon. But since it's .Net based, you will probably
> be able to code you're own maint plans.
> Anthony
>
>
e database maintenance plan I created 2 jobs:
1. Full database backup once each night
2. Transaction Log backups every 15 minutes
However, I would also like to implement a differential backup perhaps 4 time
s per day. This would save me from having to restore dozens of transaction
Log backups in the event of a failure towards the end of a day
I know that the database maintenance plan does not support differential back
ups. Is there any plans to add this in the future?
Also, if I create a separate job for differential backups, how do avoid the
T-Log backup and differential backup executing at the same time without incr
easing the T-Log interval?
What happens if both the T-Log and differential backup occur at the same ti
me?Scott,
I basically asked the same question on 07/09 with subject "backup sets to
named device". I solved my own problem like this:
Job A runs once daily at zero hours.
Step 1 creates a backup device pointing to local file with sp_addumpdevice.
Step 2 performs full backup with BACKUP DATABASE.
Step 3 removes old backup devices with sp_dropdevice.
Job B runs daily every 4 hours.
Step 1 performs diff backup with BACKUP DATABASE.
Job C runs daily every 10 minutes.
Step 1 performs log backup with BACKUP DATABASE.
Each job uses datetime and string functions to create the device and file
names, such as NorthWind_YYYYMMDD and NorthWind_db_YYYYMMDD.bak. For step 3
of Job A, I query sysdevices to get the names of old devices. You can
determine how many devices and associated backups remain based on the query.
I end up with complete backups for each day encapsulated in a single file
which gets written to tape and stored offsite.
It's working like a charm. I'll post the detailed t-sql if you need it.
Anthony|||Scott,
Sorry about not really answering your questions in the previous post. I
have had backup jobs executing at the same time and didn't see any problems.
But for my previous suggestion, I have full at zero hours, logs every 10
minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
04:00 to 23:59. Now none of the jobs intersect.
I haven't looked at Yukon. But since it's .Net based, you will probably
be able to code you're own maint plans.
Anthony|||Thanks Anthony, I would love to see the T-SQL. It sounds very creative.
My only concern is if both the transaction log and the differential backups
execute at the same time, will it cause conflicts somehow.
"Anthony" wrote:
> Scott,
> Sorry about not really answering your questions in the previous post. I
> have had backup jobs executing at the same time and didn't see any problem
s.
> But for my previous suggestion, I have full at zero hours, logs every 10
> minutes starting at 00:05 to 23:59, and diffs every 4 hours starting at
> 04:00 to 23:59. Now none of the jobs intersect.
> I haven't looked at Yukon. But since it's .Net based, you will probably
> be able to code you're own maint plans.
> Anthony
>
>
Subscribe to:
Posts (Atom)