Differencial Backups

In all documentation I have read Differencial backups are completed as a
backup added on to the initial backup. My questions are:
Will I run into any problems if my differencials are separate from the
initial backup?
If I need to restore using the above method, would I need to restore the
initial backup and the differencial (plus transaction backups) from the
last initial backup or do I still need to restore all backups to the
point of failure?
Finally: Why doesn't SQL Server have differencials as part of the
maintenance plan?
Thank you for any assistance,
Billsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
news:3FE9B746.A55BB0FE@.domail.maricopa.edu...
> In all documentation I have read Differencial backups are completed as a
> backup added on to the initial backup. My questions are:
> Will I run into any problems if my differencials are separate from the
> initial backup?
>
If (by separate) you mean in a different file, no, it doesn't matter if they
are in the same or a different file or tape.
> If I need to restore using the above method, would I need to restore the
> initial backup and the differencial (plus transaction backups) from the
> last initial backup or do I still need to restore all backups to the
> point of failure?
To restore,
1. Restore the most recent whole database backup.
2. Restore the most recent differential backup
3. Restore each transaction log which completed since the last differential
backup...
> Finally: Why doesn't SQL Server have differencials as part of the
> maintenance plan?
>
Good question... Seems like it should be...
> Thank you for any assistance,
> Bill|||Thank you Wayne I appreciate your assistance.
Wayne Snyder wrote:
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
> news:3FE9B746.A55BB0FE@.domail.maricopa.edu...
> > In all documentation I have read Differencial backups are completed as a
> > backup added on to the initial backup. My questions are:
> >
> > Will I run into any problems if my differencials are separate from the
> > initial backup?
> >
> If (by separate) you mean in a different file, no, it doesn't matter if they
> are in the same or a different file or tape.
> > If I need to restore using the above method, would I need to restore the
> > initial backup and the differencial (plus transaction backups) from the
> > last initial backup or do I still need to restore all backups to the
> > point of failure?
> To restore,
> 1. Restore the most recent whole database backup.
> 2. Restore the most recent differential backup
> 3. Restore each transaction log which completed since the last differential
> backup...
> >
> > Finally: Why doesn't SQL Server have differencials as part of the
> > maintenance plan?
> >
> Good question... Seems like it should be...
> > Thank you for any assistance,
> > Bill|||No problem...
Have a great Christmas!
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
news:3FE9BCC9.C5DA436D@.domail.maricopa.edu...
> Thank you Wayne I appreciate your assistance.
> Wayne Snyder wrote:
> >
> > see inline
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
> > news:3FE9B746.A55BB0FE@.domail.maricopa.edu...
> > > In all documentation I have read Differencial backups are completed as
a
> > > backup added on to the initial backup. My questions are:
> > >
> > > Will I run into any problems if my differencials are separate from the
> > > initial backup?
> > >
> > If (by separate) you mean in a different file, no, it doesn't matter if
they
> > are in the same or a different file or tape.
> >
> > > If I need to restore using the above method, would I need to restore
the
> > > initial backup and the differencial (plus transaction backups) from
the
> > > last initial backup or do I still need to restore all backups to the
> > > point of failure?
> >
> > To restore,
> > 1. Restore the most recent whole database backup.
> > 2. Restore the most recent differential backup
> > 3. Restore each transaction log which completed since the last
differential
> > backup...
> > >
> > > Finally: Why doesn't SQL Server have differencials as part of the
> > > maintenance plan?
> > >
> > Good question... Seems like it should be...
> > > Thank you for any assistance,
> > > Bill|||On Wed, 24 Dec 2003 08:56:54 -0700, William Enloe
<william.enloe@.domail.maricopa.edu> wrote:
>In all documentation I have read Differencial backups are completed as a
>backup added on to the initial backup. My questions are:
>Will I run into any problems if my differencials are separate from the
>initial backup?
Do you mean physically separate, or do you mean in separate files, or
what?
>If I need to restore using the above method, would I need to restore the
>initial backup and the differencial (plus transaction backups) from the
>last initial backup or do I still need to restore all backups to the
>point of failure?
The rules for restores are:
Restore your 'best' (usually: "latest") full backup.
If you have one or more differential backups that were taken between
your best full backup, and the next backup taken, apply *ONLY* the
last differential backup.
Then apply all transaction log backups
1) Since your differential backup, if you've restored a differential
backup, or
2) since your full backup, if you haven't restored a differential
backup
You need to use some common sense here, but the crucial thing is:
Restore
One full backup, always.
Never more than one differential, if you have a useful one
All transaction logs since the last backup (full or differential) you
restored.
>Finally: Why doesn't SQL Server have differencials as part of the
>maintenance plan?
I wish I knew. I suppose that the assumption is that if you are
sophisticated enough to need differentials, you're sophisticated
enough to script them yourself.
(Well, I *AM*, but I don't know how to delete the buggers after enough
time has passed! I suppose I could try using a .bat file and scheduler
and the SQL cli interface, but *geez*.)