Backup issue

Hello,
Windows 2000 SP4 Fully Patched
SQL Server 2000 SP3a + MS03-031
When I create a new database and try to make a differential backup without
ever having made a (full) database backup it fails. This is OK and is what
I expect and want. However if I delete the backups en use sp_delete_backup
to remove the entries from msdb it allows the creation of differential
backups even if there is no record of there being a full backup in the msdb
database. Probably in the database itself their is a "bit" that gets set
but I find it dangerous and need to make sure that in the latter case a full
backup is requierd. Any hints?
Thank you.Your differential backup is useless without a full backup.
The differential backup contains only the changes (the changed extents)
since the last full backup.
A full backup is always your starting point. from there you can use
differential, file/filegroup or transaction log backups to capture the
changes to the DB.
You should regularly make full backups so that if you need to restore the
DB, the restore process will be shorter.
HTH
"John Koswalski" <john.koswalski@.shell.nl> wrote in message
news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Windows 2000 SP4 Fully Patched
> SQL Server 2000 SP3a + MS03-031
> When I create a new database and try to make a differential backup without
> ever having made a (full) database backup it fails. This is OK and is
what
> I expect and want. However if I delete the backups en use
sp_delete_backup
> to remove the entries from msdb it allows the creation of differential
> backups even if there is no record of there being a full backup in the
msdb
> database. Probably in the database itself their is a "bit" that gets set
> but I find it dangerous and need to make sure that in the latter case a
full
> backup is requierd. Any hints?
> Thank you.
>|||Indeed Amy,
That's why I'm surprised that when deleting all information using
sp_delete_backuphistory it still allows me to make a differential backup. I
wonder why that is, since it is indeed useless!
"Amy" <l.a@.usa.com> wrote in message
news:ONxmud1YDHA.440@.tk2msftngp13.phx.gbl...
> Your differential backup is useless without a full backup.
> The differential backup contains only the changes (the changed extents)
> since the last full backup.
> A full backup is always your starting point. from there you can use
> differential, file/filegroup or transaction log backups to capture the
> changes to the DB.
> You should regularly make full backups so that if you need to restore the
> DB, the restore process will be shorter.
> HTH
> "John Koswalski" <john.koswalski@.shell.nl> wrote in message
> news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > Windows 2000 SP4 Fully Patched
> > SQL Server 2000 SP3a + MS03-031
> >
> > When I create a new database and try to make a differential backup
without
> > ever having made a (full) database backup it fails. This is OK and is
> what
> > I expect and want. However if I delete the backups en use
> sp_delete_backup
> > to remove the entries from msdb it allows the creation of differential
> > backups even if there is no record of there being a full backup in the
> msdb
> > database. Probably in the database itself their is a "bit" that gets
set
> > but I find it dangerous and need to make sure that in the latter case a
> full
> > backup is requierd. Any hints?
> >
> > Thank you.
> >
> >
>|||Deleting the backup history does not necessarily delete
the actual full database backup itself. A sequence
number is maintanied that shows whether a data page has
been modified since the last full backup was performed,
and if the page has been modified, it (and it's entire
extent) is included in the following differential backup.
This mechanism is separate from the backup history, which
is used primarily to make it easier to restore specific
backup sessions in the proper order.
A situation just as problematic is suppose that you do
not delete the backup history, but you do delete or
discard the full backup itself. You still can't perform
a restore with just the differential backup, although
clearly you can perform the backup.
I realize this doesn't help much, but it is smoething to
bear in mind. The system will allow the differential
backup, and it is up to the dba to ensure that the
precedign full database backup will be available in the
event that a restore is needed. Look at it this way:
If you lose the preceding full database backup, it
doesn't matter whether system allows you to perform the
differential backup or not, because you have no restore
capability either way.
Just some thoughts,
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Indeed Amy,
>That's why I'm surprised that when deleting all
information using
>sp_delete_backuphistory it still allows me to make a
differential backup. I
>wonder why that is, since it is indeed useless!
>"Amy" <l.a@.usa.com> wrote in message
>news:ONxmud1YDHA.440@.tk2msftngp13.phx.gbl...
>> Your differential backup is useless without a full
backup.
>> The differential backup contains only the changes (the
changed extents)
>> since the last full backup.
>> A full backup is always your starting point. from
there you can use
>> differential, file/filegroup or transaction log
backups to capture the
>> changes to the DB.
>> You should regularly make full backups so that if you
need to restore the
>> DB, the restore process will be shorter.
>> HTH
>> "John Koswalski" <john.koswalski@.shell.nl> wrote in
message
>> news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
>> > Hello,
>> >
>> > Windows 2000 SP4 Fully Patched
>> > SQL Server 2000 SP3a + MS03-031
>> >
>> > When I create a new database and try to make a
differential backup
>without
>> > ever having made a (full) database backup it fails.
This is OK and is
>> what
>> > I expect and want. However if I delete the backups
en use
>> sp_delete_backup
>> > to remove the entries from msdb it allows the
creation of differential
>> > backups even if there is no record of there being a
full backup in the
>> msdb
>> > database. Probably in the database itself their is
a "bit" that gets
>set
>> > but I find it dangerous and need to make sure that
in the latter case a
>> full
>> > backup is requierd. Any hints?
>> >
>> > Thank you.
>> >
>> >
>>
>
>.
>|||Hello Matthew,
You're right. But there is, in my opinion a little difference, I choose to
delete the history ... I control it or I like to believe I control it. In
the end, when you can't restore a backup it doesn't make a difference ...
practically ...
Thanks for your thoughts
"Matthew Bando" <BandoM@.CSCTechnologies.com> wrote in message
news:03f801c3635e$7b0051c0$a101280a@.phx.gbl...
> Deleting the backup history does not necessarily delete
> the actual full database backup itself. A sequence
> number is maintanied that shows whether a data page has
> been modified since the last full backup was performed,
> and if the page has been modified, it (and it's entire
> extent) is included in the following differential backup.
> This mechanism is separate from the backup history, which
> is used primarily to make it easier to restore specific
> backup sessions in the proper order.
> A situation just as problematic is suppose that you do
> not delete the backup history, but you do delete or
> discard the full backup itself. You still can't perform
> a restore with just the differential backup, although
> clearly you can perform the backup.
> I realize this doesn't help much, but it is smoething to
> bear in mind. The system will allow the differential
> backup, and it is up to the dba to ensure that the
> precedign full database backup will be available in the
> event that a restore is needed. Look at it this way:
> If you lose the preceding full database backup, it
> doesn't matter whether system allows you to perform the
> differential backup or not, because you have no restore
> capability either way.
> Just some thoughts,
> Matthew Bando
> BandoM@.CSCTechnologies.com
>
> >--Original Message--
> >Indeed Amy,
> >
> >That's why I'm surprised that when deleting all
> information using
> >sp_delete_backuphistory it still allows me to make a
> differential backup. I
> >wonder why that is, since it is indeed useless!
> >
> >"Amy" <l.a@.usa.com> wrote in message
> >news:ONxmud1YDHA.440@.tk2msftngp13.phx.gbl...
> >> Your differential backup is useless without a full
> backup.
> >> The differential backup contains only the changes (the
> changed extents)
> >> since the last full backup.
> >> A full backup is always your starting point. from
> there you can use
> >> differential, file/filegroup or transaction log
> backups to capture the
> >> changes to the DB.
> >> You should regularly make full backups so that if you
> need to restore the
> >> DB, the restore process will be shorter.
> >>
> >> HTH
> >>
> >> "John Koswalski" <john.koswalski@.shell.nl> wrote in
> message
> >> news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
> >> > Hello,
> >> >
> >> > Windows 2000 SP4 Fully Patched
> >> > SQL Server 2000 SP3a + MS03-031
> >> >
> >> > When I create a new database and try to make a
> differential backup
> >without
> >> > ever having made a (full) database backup it fails.
> This is OK and is
> >> what
> >> > I expect and want. However if I delete the backups
> en use
> >> sp_delete_backup
> >> > to remove the entries from msdb it allows the
> creation of differential
> >> > backups even if there is no record of there being a
> full backup in the
> >> msdb
> >> > database. Probably in the database itself their is
> a "bit" that gets
> >set
> >> > but I find it dangerous and need to make sure that
> in the latter case a
> >> full
> >> > backup is requierd. Any hints?
> >> >
> >> > Thank you.
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >|||I understand the control issue entirely. I typically
perform restores from devices rather than using the
sessions that the backup history maintains. I guess
that's my way of maintaining the illusion of control! :-)
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello Matthew,
>You're right. But there is, in my opinion a little
difference, I choose to
>delete the history ... I control it or I like to believe
I control it. In
>the end, when you can't restore a backup it doesn't make
a difference ...
>practically ...
>Thanks for your thoughts
>
>"Matthew Bando" <BandoM@.CSCTechnologies.com> wrote in
message
>news:03f801c3635e$7b0051c0$a101280a@.phx.gbl...
>> Deleting the backup history does not necessarily delete
>> the actual full database backup itself. A sequence
>> number is maintanied that shows whether a data page has
>> been modified since the last full backup was performed,
>> and if the page has been modified, it (and it's entire
>> extent) is included in the following differential
backup.
>> This mechanism is separate from the backup history,
which
>> is used primarily to make it easier to restore specific
>> backup sessions in the proper order.
>> A situation just as problematic is suppose that you do
>> not delete the backup history, but you do delete or
>> discard the full backup itself. You still can't
perform
>> a restore with just the differential backup, although
>> clearly you can perform the backup.
>> I realize this doesn't help much, but it is smoething
to
>> bear in mind. The system will allow the differential
>> backup, and it is up to the dba to ensure that the
>> precedign full database backup will be available in the
>> event that a restore is needed. Look at it this way:
>> If you lose the preceding full database backup, it
>> doesn't matter whether system allows you to perform the
>> differential backup or not, because you have no restore
>> capability either way.
>> Just some thoughts,
>> Matthew Bando
>> BandoM@.CSCTechnologies.com
>>
>> >--Original Message--
>> >Indeed Amy,
>> >
>> >That's why I'm surprised that when deleting all
>> information using
>> >sp_delete_backuphistory it still allows me to make a
>> differential backup. I
>> >wonder why that is, since it is indeed useless!
>> >
>> >"Amy" <l.a@.usa.com> wrote in message
>> >news:ONxmud1YDHA.440@.tk2msftngp13.phx.gbl...
>> >> Your differential backup is useless without a full
>> backup.
>> >> The differential backup contains only the changes
(the
>> changed extents)
>> >> since the last full backup.
>> >> A full backup is always your starting point. from
>> there you can use
>> >> differential, file/filegroup or transaction log
>> backups to capture the
>> >> changes to the DB.
>> >> You should regularly make full backups so that if
you
>> need to restore the
>> >> DB, the restore process will be shorter.
>> >>
>> >> HTH
>> >>
>> >> "John Koswalski" <john.koswalski@.shell.nl> wrote in
>> message
>> >> news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
>> >> > Hello,
>> >> >
>> >> > Windows 2000 SP4 Fully Patched
>> >> > SQL Server 2000 SP3a + MS03-031
>> >> >
>> >> > When I create a new database and try to make a
>> differential backup
>> >without
>> >> > ever having made a (full) database backup it
fails.
>> This is OK and is
>> >> what
>> >> > I expect and want. However if I delete the
backups
>> en use
>> >> sp_delete_backup
>> >> > to remove the entries from msdb it allows the
>> creation of differential
>> >> > backups even if there is no record of there being
a
>> full backup in the
>> >> msdb
>> >> > database. Probably in the database itself their
is
>> a "bit" that gets
>> >set
>> >> > but I find it dangerous and need to make sure that
>> in the latter case a
>> >> full
>> >> > backup is requierd. Any hints?
>> >> >
>> >> > Thank you.
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>