Hi,
I wrote a script that theoretically does the following:
Does a weekly full backup to a file
Does a nightly differential backup to a file
Does hourly transaction logs to a seperate file per hour.
As I understand it, the differential is supposed to be based off of the full
backup. After the first nightly, is each differential still based off of
the original full back up? or is it based of the previous nights
differential, which means I'd have to keep each daily differential until the
week cycled
Thanks,
GaryA diff backup is always based on the latest full backup. This is why you do
the restore like: latest full backup, *latest* diff backup and each
subsequent log backups.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Gary" <n> wrote in message news:%23mUwyzp6DHA.1556@.tk2msftngp13.phx.gbl...
> Hi,
> I wrote a script that theoretically does the following:
> Does a weekly full backup to a file
> Does a nightly differential backup to a file
> Does hourly transaction logs to a seperate file per hour.
> As I understand it, the differential is supposed to be based off of the
full
> backup. After the first nightly, is each differential still based off of
> the original full back up? or is it based of the previous nights
> differential, which means I'd have to keep each daily differential until
the
> week cycled
> Thanks,
> Gary
>|||Thanks.
I was pretty sure that's how it worked, I just wanted a second opinion
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%231UIe4p6DHA.3648@.TK2MSFTNGP11.phx.gbl...
> A diff backup is always based on the latest full backup. This is why you
do
> the restore like: latest full backup, *latest* diff backup and each
> subsequent log backups.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Gary" <n> wrote in message
news:%23mUwyzp6DHA.1556@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I wrote a script that theoretically does the following:
> >
> > Does a weekly full backup to a file
> > Does a nightly differential backup to a file
> > Does hourly transaction logs to a seperate file per hour.
> >
> > As I understand it, the differential is supposed to be based off of the
> full
> > backup. After the first nightly, is each differential still based off
of
> > the original full back up? or is it based of the previous nights
> > differential, which means I'd have to keep each daily differential until
> the
> > week cycled
> >
> > Thanks,
> > Gary
> >
> >
>
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Wednesday, March 21, 2012
Differntial Backups
Differntial Backups
Hi,
I wrote a script that theoretically does the following:
Does a weekly full backup to a file
Does a nightly differential backup to a file
Does hourly transaction logs to a seperate file per hour.
As I understand it, the differential is supposed to be based off of the full
backup. After the first nightly, is each differential still based off of
the original full back up? or is it based of the previous nights
differential, which means I'd have to keep each daily differential until the
week cycled
Thanks,
GaryA diff backup is always based on the latest full backup. This is why you do
the restore like: latest full backup, *latest* diff backup and each
subsequent log backups.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Gary" <n> wrote in message news:%23mUwyzp6DHA.1556@.tk2msftngp13.phx.gbl...
full
the
I was pretty sure that's how it worked, I just wanted a second opinion
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%231UIe4p6DHA.3648@.TK2MSFTNGP11.phx.gbl...
do
http://groups.google.com/groups?oi=...ublic.sqlserver
news:%23mUwyzp6DHA.1556@.tk2msftngp13.phx.gbl...
I wrote a script that theoretically does the following:
Does a weekly full backup to a file
Does a nightly differential backup to a file
Does hourly transaction logs to a seperate file per hour.
As I understand it, the differential is supposed to be based off of the full
backup. After the first nightly, is each differential still based off of
the original full back up? or is it based of the previous nights
differential, which means I'd have to keep each daily differential until the
week cycled
Thanks,
GaryA diff backup is always based on the latest full backup. This is why you do
the restore like: latest full backup, *latest* diff backup and each
subsequent log backups.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Gary" <n> wrote in message news:%23mUwyzp6DHA.1556@.tk2msftngp13.phx.gbl...
quote:
> Hi,
> I wrote a script that theoretically does the following:
> Does a weekly full backup to a file
> Does a nightly differential backup to a file
> Does hourly transaction logs to a seperate file per hour.
> As I understand it, the differential is supposed to be based off of the
full
quote:
> backup. After the first nightly, is each differential still based off of
> the original full back up? or is it based of the previous nights
> differential, which means I'd have to keep each daily differential until
the
quote:|||Thanks.
> week cycled
> Thanks,
> Gary
>
I was pretty sure that's how it worked, I just wanted a second opinion
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%231UIe4p6DHA.3648@.TK2MSFTNGP11.phx.gbl...
quote:
> A diff backup is always based on the latest full backup. This is why you
do
quote:
> the restore like: latest full backup, *latest* diff backup and each
> subsequent log backups.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
quote:
>
> "Gary" <n> wrote in message
news:%23mUwyzp6DHA.1556@.tk2msftngp13.phx.gbl...
quote:
> full
of[QUOTE]
> the
>
Labels:
backup,
backups,
database,
differential,
differntial,
filedoes,
followingdoes,
hourly,
microsoft,
mysql,
nightly,
oracle,
script,
server,
sql,
theoretically,
weekly
Differntial Backup doesn't work
I setup Differntial Backup through EM but it doesn't
work. The backup file added up 1G in every back up and
end up with running out of space.
Can anybody tell me what's wrong?
Frankprobably you are adding backup into existing backup over and over, causing
it increases until no more empty sapce. You need to review your backup
strategy.
"Frank Jiang" <frank@.discussions.microsoft.com> wrote in message
news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
backup every 20 minutes, append to media.
I check all the setting of the differential backup in
Enterprise Manager and didn't find anything wrong.
Frank
and over, causing
review your backup
message
certain time.
Can you do differential backup every 4 hrs and do log backup every 20
minutes to a separate backup device? It may help.
"Frank J" <frank@.discussions.microsoft.com> wrote in message
news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...[QUOTE]
> I do a complete backup once eveyday and differential
> backup every 20 minutes, append to media.
> I check all the setting of the differential backup in
> Enterprise Manager and didn't find anything wrong.
> Frank
>
> and over, causing
> review your backup
> message|||It never happened so far that we need to restore DB. But
theoretically, we can resote DB from a complete backup
and on top of that we do recover from differential
backup. Thats why we do a complete backup everyday and
followed by 20 minutes differential backup.
Transaction log is huge and should we backup it?
Frank
restore DB to a
backup every 20
message
database modified after the last database backup.
so to restore you just need a full backup and the last differential backup.
You keep adding differential backup into same backup device every 20 minutes
then no wonder it keeps growing 'til you are out of space.
depend on your needs. I always backup the log.
"Frank J" <anonymous@.discussions.microsoft.com> wrote in message
news:2cbc01c3e1e7$7aef6470$a101280a@.phx.gbl...[QUOTE]
> It never happened so far that we need to restore DB. But
> theoretically, we can resote DB from a complete backup
> and on top of that we do recover from differential
> backup. Thats why we do a complete backup everyday and
> followed by 20 minutes differential backup.
> Transaction log is huge and should we backup it?
> Frank
>
> restore DB to a
> backup every 20
> message
> over
> in
> and
work. The backup file added up 1G in every back up and
end up with running out of space.
Can anybody tell me what's wrong?
Frankprobably you are adding backup into existing backup over and over, causing
it increases until no more empty sapce. You need to review your backup
strategy.
"Frank Jiang" <frank@.discussions.microsoft.com> wrote in message
news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
quote:|||I do a complete backup once eveyday and differential
> I setup Differntial Backup through EM but it doesn't
> work. The backup file added up 1G in every back up and
> end up with running out of space.
> Can anybody tell me what's wrong?
> Frank
backup every 20 minutes, append to media.
I check all the setting of the differential backup in
Enterprise Manager and didn't find anything wrong.
Frank
quote:
>--Original Message--
>probably you are adding backup into existing backup over
and over, causing
quote:
>it increases until no more empty sapce. You need to
review your backup
quote:
>strategy.
>"Frank Jiang" <frank@.discussions.microsoft.com> wrote in
message
quote:|||with differential backup you don't have the option to restore DB to a
>news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
>
>.
>
certain time.
Can you do differential backup every 4 hrs and do log backup every 20
minutes to a separate backup device? It may help.
"Frank J" <frank@.discussions.microsoft.com> wrote in message
news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...[QUOTE]
> I do a complete backup once eveyday and differential
> backup every 20 minutes, append to media.
> I check all the setting of the differential backup in
> Enterprise Manager and didn't find anything wrong.
> Frank
>
> and over, causing
> review your backup
> message|||It never happened so far that we need to restore DB. But
theoretically, we can resote DB from a complete backup
and on top of that we do recover from differential
backup. Thats why we do a complete backup everyday and
followed by 20 minutes differential backup.
Transaction log is huge and should we backup it?
Frank
quote:
>--Original Message--
>with differential backup you don't have the option to
restore DB to a
quote:
>certain time.
>Can you do differential backup every 4 hrs and do log
backup every 20
quote:
>minutes to a separate backup device? It may help.
>
>"Frank J" <frank@.discussions.microsoft.com> wrote in
message
quote:|||from BOL: A differential backup creates a copy of all the pages in a
>news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
over[QUOTE]
in[QUOTE]
and[QUOTE]
>
>.
>
database modified after the last database backup.
so to restore you just need a full backup and the last differential backup.
You keep adding differential backup into same backup device every 20 minutes
then no wonder it keeps growing 'til you are out of space.
quote:
> Transaction log is huge and should we backup it?
depend on your needs. I always backup the log.
"Frank J" <anonymous@.discussions.microsoft.com> wrote in message
news:2cbc01c3e1e7$7aef6470$a101280a@.phx.gbl...[QUOTE]
> It never happened so far that we need to restore DB. But
> theoretically, we can resote DB from a complete backup
> and on top of that we do recover from differential
> backup. Thats why we do a complete backup everyday and
> followed by 20 minutes differential backup.
> Transaction log is huge and should we backup it?
> Frank
>
> restore DB to a
> backup every 20
> message
> over
> in
> and
Differntial Backup doesn't work
I setup Differntial Backup through EM but it doesn't
work. The backup file added up 1G in every back up and
end up with running out of space.
Can anybody tell me what's wrong?
Frankprobably you are adding backup into existing backup over and over, causing
it increases until no more empty sapce. You need to review your backup
strategy.
"Frank Jiang" <frank@.discussions.microsoft.com> wrote in message
news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
> I setup Differntial Backup through EM but it doesn't
> work. The backup file added up 1G in every back up and
> end up with running out of space.
> Can anybody tell me what's wrong?
> Frank|||I do a complete backup once eveyday and differential
backup every 20 minutes, append to media.
I check all the setting of the differential backup in
Enterprise Manager and didn't find anything wrong.
Frank
>--Original Message--
>probably you are adding backup into existing backup over
and over, causing
>it increases until no more empty sapce. You need to
review your backup
>strategy.
>"Frank Jiang" <frank@.discussions.microsoft.com> wrote in
message
>news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
>> I setup Differntial Backup through EM but it doesn't
>> work. The backup file added up 1G in every back up and
>> end up with running out of space.
>> Can anybody tell me what's wrong?
>> Frank
>
>.
>|||with differential backup you don't have the option to restore DB to a
certain time.
Can you do differential backup every 4 hrs and do log backup every 20
minutes to a separate backup device? It may help.
"Frank J" <frank@.discussions.microsoft.com> wrote in message
news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
> I do a complete backup once eveyday and differential
> backup every 20 minutes, append to media.
> I check all the setting of the differential backup in
> Enterprise Manager and didn't find anything wrong.
> Frank
> >--Original Message--
> >probably you are adding backup into existing backup over
> and over, causing
> >it increases until no more empty sapce. You need to
> review your backup
> >strategy.
> >
> >"Frank Jiang" <frank@.discussions.microsoft.com> wrote in
> message
> >news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
> >> I setup Differntial Backup through EM but it doesn't
> >> work. The backup file added up 1G in every back up and
> >> end up with running out of space.
> >>
> >> Can anybody tell me what's wrong?
> >>
> >> Frank
> >
> >
> >.
> >|||It never happened so far that we need to restore DB. But
theoretically, we can resote DB from a complete backup
and on top of that we do recover from differential
backup. Thats why we do a complete backup everyday and
followed by 20 minutes differential backup.
Transaction log is huge and should we backup it?
Frank
>--Original Message--
>with differential backup you don't have the option to
restore DB to a
>certain time.
>Can you do differential backup every 4 hrs and do log
backup every 20
>minutes to a separate backup device? It may help.
>
>"Frank J" <frank@.discussions.microsoft.com> wrote in
message
>news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
>> I do a complete backup once eveyday and differential
>> backup every 20 minutes, append to media.
>> I check all the setting of the differential backup in
>> Enterprise Manager and didn't find anything wrong.
>> Frank
>> >--Original Message--
>> >probably you are adding backup into existing backup
over
>> and over, causing
>> >it increases until no more empty sapce. You need to
>> review your backup
>> >strategy.
>> >
>> >"Frank Jiang" <frank@.discussions.microsoft.com> wrote
in
>> message
>> >news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
>> >> I setup Differntial Backup through EM but it doesn't
>> >> work. The backup file added up 1G in every back up
and
>> >> end up with running out of space.
>> >>
>> >> Can anybody tell me what's wrong?
>> >>
>> >> Frank
>> >
>> >
>> >.
>> >
>
>.
>|||from BOL: A differential backup creates a copy of all the pages in a
database modified after the last database backup.
so to restore you just need a full backup and the last differential backup.
You keep adding differential backup into same backup device every 20 minutes
then no wonder it keeps growing 'til you are out of space.
> Transaction log is huge and should we backup it?
depend on your needs. I always backup the log.
"Frank J" <anonymous@.discussions.microsoft.com> wrote in message
news:2cbc01c3e1e7$7aef6470$a101280a@.phx.gbl...
> It never happened so far that we need to restore DB. But
> theoretically, we can resote DB from a complete backup
> and on top of that we do recover from differential
> backup. Thats why we do a complete backup everyday and
> followed by 20 minutes differential backup.
> Transaction log is huge and should we backup it?
> Frank
> >--Original Message--
> >with differential backup you don't have the option to
> restore DB to a
> >certain time.
> >Can you do differential backup every 4 hrs and do log
> backup every 20
> >minutes to a separate backup device? It may help.
> >
> >
> >"Frank J" <frank@.discussions.microsoft.com> wrote in
> message
> >news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
> >> I do a complete backup once eveyday and differential
> >> backup every 20 minutes, append to media.
> >> I check all the setting of the differential backup in
> >> Enterprise Manager and didn't find anything wrong.
> >>
> >> Frank
> >>
> >> >--Original Message--
> >> >probably you are adding backup into existing backup
> over
> >> and over, causing
> >> >it increases until no more empty sapce. You need to
> >> review your backup
> >> >strategy.
> >> >
> >> >"Frank Jiang" <frank@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
> >> >> I setup Differntial Backup through EM but it doesn't
> >> >> work. The backup file added up 1G in every back up
> and
> >> >> end up with running out of space.
> >> >>
> >> >> Can anybody tell me what's wrong?
> >> >>
> >> >> Frank
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >sql
work. The backup file added up 1G in every back up and
end up with running out of space.
Can anybody tell me what's wrong?
Frankprobably you are adding backup into existing backup over and over, causing
it increases until no more empty sapce. You need to review your backup
strategy.
"Frank Jiang" <frank@.discussions.microsoft.com> wrote in message
news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
> I setup Differntial Backup through EM but it doesn't
> work. The backup file added up 1G in every back up and
> end up with running out of space.
> Can anybody tell me what's wrong?
> Frank|||I do a complete backup once eveyday and differential
backup every 20 minutes, append to media.
I check all the setting of the differential backup in
Enterprise Manager and didn't find anything wrong.
Frank
>--Original Message--
>probably you are adding backup into existing backup over
and over, causing
>it increases until no more empty sapce. You need to
review your backup
>strategy.
>"Frank Jiang" <frank@.discussions.microsoft.com> wrote in
message
>news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
>> I setup Differntial Backup through EM but it doesn't
>> work. The backup file added up 1G in every back up and
>> end up with running out of space.
>> Can anybody tell me what's wrong?
>> Frank
>
>.
>|||with differential backup you don't have the option to restore DB to a
certain time.
Can you do differential backup every 4 hrs and do log backup every 20
minutes to a separate backup device? It may help.
"Frank J" <frank@.discussions.microsoft.com> wrote in message
news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
> I do a complete backup once eveyday and differential
> backup every 20 minutes, append to media.
> I check all the setting of the differential backup in
> Enterprise Manager and didn't find anything wrong.
> Frank
> >--Original Message--
> >probably you are adding backup into existing backup over
> and over, causing
> >it increases until no more empty sapce. You need to
> review your backup
> >strategy.
> >
> >"Frank Jiang" <frank@.discussions.microsoft.com> wrote in
> message
> >news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
> >> I setup Differntial Backup through EM but it doesn't
> >> work. The backup file added up 1G in every back up and
> >> end up with running out of space.
> >>
> >> Can anybody tell me what's wrong?
> >>
> >> Frank
> >
> >
> >.
> >|||It never happened so far that we need to restore DB. But
theoretically, we can resote DB from a complete backup
and on top of that we do recover from differential
backup. Thats why we do a complete backup everyday and
followed by 20 minutes differential backup.
Transaction log is huge and should we backup it?
Frank
>--Original Message--
>with differential backup you don't have the option to
restore DB to a
>certain time.
>Can you do differential backup every 4 hrs and do log
backup every 20
>minutes to a separate backup device? It may help.
>
>"Frank J" <frank@.discussions.microsoft.com> wrote in
message
>news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
>> I do a complete backup once eveyday and differential
>> backup every 20 minutes, append to media.
>> I check all the setting of the differential backup in
>> Enterprise Manager and didn't find anything wrong.
>> Frank
>> >--Original Message--
>> >probably you are adding backup into existing backup
over
>> and over, causing
>> >it increases until no more empty sapce. You need to
>> review your backup
>> >strategy.
>> >
>> >"Frank Jiang" <frank@.discussions.microsoft.com> wrote
in
>> message
>> >news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
>> >> I setup Differntial Backup through EM but it doesn't
>> >> work. The backup file added up 1G in every back up
and
>> >> end up with running out of space.
>> >>
>> >> Can anybody tell me what's wrong?
>> >>
>> >> Frank
>> >
>> >
>> >.
>> >
>
>.
>|||from BOL: A differential backup creates a copy of all the pages in a
database modified after the last database backup.
so to restore you just need a full backup and the last differential backup.
You keep adding differential backup into same backup device every 20 minutes
then no wonder it keeps growing 'til you are out of space.
> Transaction log is huge and should we backup it?
depend on your needs. I always backup the log.
"Frank J" <anonymous@.discussions.microsoft.com> wrote in message
news:2cbc01c3e1e7$7aef6470$a101280a@.phx.gbl...
> It never happened so far that we need to restore DB. But
> theoretically, we can resote DB from a complete backup
> and on top of that we do recover from differential
> backup. Thats why we do a complete backup everyday and
> followed by 20 minutes differential backup.
> Transaction log is huge and should we backup it?
> Frank
> >--Original Message--
> >with differential backup you don't have the option to
> restore DB to a
> >certain time.
> >Can you do differential backup every 4 hrs and do log
> backup every 20
> >minutes to a separate backup device? It may help.
> >
> >
> >"Frank J" <frank@.discussions.microsoft.com> wrote in
> message
> >news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
> >> I do a complete backup once eveyday and differential
> >> backup every 20 minutes, append to media.
> >> I check all the setting of the differential backup in
> >> Enterprise Manager and didn't find anything wrong.
> >>
> >> Frank
> >>
> >> >--Original Message--
> >> >probably you are adding backup into existing backup
> over
> >> and over, causing
> >> >it increases until no more empty sapce. You need to
> >> review your backup
> >> >strategy.
> >> >
> >> >"Frank Jiang" <frank@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
> >> >> I setup Differntial Backup through EM but it doesn't
> >> >> work. The backup file added up 1G in every back up
> and
> >> >> end up with running out of space.
> >> >>
> >> >> Can anybody tell me what's wrong?
> >> >>
> >> >> Frank
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >sql
Differntial Backup
Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
|||This is exactly what I was looking for.
Thanks
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>
|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>
|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
[vbcol=seagreen]
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
|||This is exactly what I was looking for.
Thanks
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>
|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>
|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
[vbcol=seagreen]
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
Labels:
articles,
backup,
database,
differntial,
microsoft,
mysql,
oracle,
particular,
questionhow,
readmsdn,
server,
sql,
states
Differntial Backup
Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.Hi
have you seen this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||This is exactly what I was looking for.
Thanks
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> > Question
> >
> > How exactly does the technology of a differntial backup work. I have read
> > msdn articles, etc... But nothing states in particular how the technology
> > itself operates. It doesn't appear to use the log file.
> >
> > Since it is a single file (No secondaries used) how can it create a
> > differential file of updates that have occured since the last full backup?
> >
> > --
> > Paul Bergson
> > MVP - Directory Services
> > MCT, MCSE, MCSA, Security+, BS CSci
> > 2003, 2000 (Early Achiever), NT
> >
> > http://www.pbbergs.com
> >
> > This posting is provided "AS IS" with no warranties or rights.
>|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
--
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>> Question
>> How exactly does the technology of a differntial backup work. I have
>> read
>> msdn articles, etc... But nothing states in particular how the technology
>> itself operates. It doesn't appear to use the log file.
>> Since it is a single file (No secondaries used) how can it create a
>> differential file of updates that have occured since the last full
>> backup?
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> This posting is provided "AS IS" with no warranties or rights.
>|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
> > The GAM (global allocation map) keeps track of which Extents have been
> > changed since the last full backup. Those are the ones written by a
> > differential backup.
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> >
> > "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> > news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> > > Question
> > >
> > > How exactly does the technology of a differntial backup work. I have read
> > > msdn articles, etc... But nothing states in particular how the technology
> > > itself operates. It doesn't appear to use the log file.
> > >
> > > Since it is a single file (No secondaries used) how can it create a
> > > differential file of updates that have occured since the last full backup?
> > >
> > > --
> > > Paul Bergson
> > > MVP - Directory Services
> > > MCT, MCSE, MCSA, Security+, BS CSci
> > > 2003, 2000 (Early Achiever), NT
> > >
> > > http://www.pbbergs.com
> > >
> > > This posting is provided "AS IS" with no warranties or rights.
> >
> >
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.Hi
have you seen this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||This is exactly what I was looking for.
Thanks
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> > Question
> >
> > How exactly does the technology of a differntial backup work. I have read
> > msdn articles, etc... But nothing states in particular how the technology
> > itself operates. It doesn't appear to use the log file.
> >
> > Since it is a single file (No secondaries used) how can it create a
> > differential file of updates that have occured since the last full backup?
> >
> > --
> > Paul Bergson
> > MVP - Directory Services
> > MCT, MCSE, MCSA, Security+, BS CSci
> > 2003, 2000 (Early Achiever), NT
> >
> > http://www.pbbergs.com
> >
> > This posting is provided "AS IS" with no warranties or rights.
>|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
--
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>> Question
>> How exactly does the technology of a differntial backup work. I have
>> read
>> msdn articles, etc... But nothing states in particular how the technology
>> itself operates. It doesn't appear to use the log file.
>> Since it is a single file (No secondaries used) how can it create a
>> differential file of updates that have occured since the last full
>> backup?
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> This posting is provided "AS IS" with no warranties or rights.
>|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
> > The GAM (global allocation map) keeps track of which Extents have been
> > changed since the last full backup. Those are the ones written by a
> > differential backup.
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> >
> > "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> > news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> > > Question
> > >
> > > How exactly does the technology of a differntial backup work. I have read
> > > msdn articles, etc... But nothing states in particular how the technology
> > > itself operates. It doesn't appear to use the log file.
> > >
> > > Since it is a single file (No secondaries used) how can it create a
> > > differential file of updates that have occured since the last full backup?
> > >
> > > --
> > > Paul Bergson
> > > MVP - Directory Services
> > > MCT, MCSE, MCSA, Security+, BS CSci
> > > 2003, 2000 (Early Achiever), NT
> > >
> > > http://www.pbbergs.com
> > >
> > > This posting is provided "AS IS" with no warranties or rights.
> >
> >
Differntial Backup
Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.Hi
have you seen this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||This is exactly what I was looking for.
Thanks
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in
a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
[vbcol=seagreen]
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
>
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.Hi
have you seen this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||This is exactly what I was looking for.
Thanks
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in
a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
[vbcol=seagreen]
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
>
Labels:
articles,
backup,
database,
differntial,
microsoft,
mysql,
oracle,
particular,
questionhow,
readmsdn,
server,
sql,
states
Monday, March 19, 2012
differentials question - can i skip a full backup?
Hi,
I have a SqlExpress app and would like to start using differential
backups (we currently always use full backups) for our auto-backup
utility. The problem is that the users can create their own backups
(always full). My auto-backup utility will not use these files. I
control the auto-backups and hide them from the user.
Can I somehow tell SqlExpress to ignore any user-created backups and
reference my differentials off MY full backups?
thanks very much!
Deanna
That's GREAT news. The user backs up via the application, so assuming
there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
research. If there's NOT and you have another way to programmatically
cause the COPY_ONLY option please let me know!
thanks so much!
Deanna
|||Tibor,
Thanks so much. I was just reading about SMO this morning (we JUST
moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
guess I should stick with DMO and use the .ExecuteImmediate. If anyone
knows a way to accomplish this with SMO please let me know. I'd prefer
to dump DMO.
Thanks for all the help!
Deanna
I have a SqlExpress app and would like to start using differential
backups (we currently always use full backups) for our auto-backup
utility. The problem is that the users can create their own backups
(always full). My auto-backup utility will not use these files. I
control the auto-backups and hide them from the user.
Can I somehow tell SqlExpress to ignore any user-created backups and
reference my differentials off MY full backups?
thanks very much!
Deanna
That's GREAT news. The user backs up via the application, so assuming
there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
research. If there's NOT and you have another way to programmatically
cause the COPY_ONLY option please let me know!
thanks so much!
Deanna
|||Tibor,
Thanks so much. I was just reading about SMO this morning (we JUST
moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
guess I should stick with DMO and use the .ExecuteImmediate. If anyone
knows a way to accomplish this with SMO please let me know. I'd prefer
to dump DMO.
Thanks for all the help!
Deanna
Labels:
app,
auto-backuputility,
backup,
backups,
database,
differentialbackups,
differentials,
microsoft,
mysql,
oracle,
server,
skip,
sql,
sqlexpress
differentials question - can i skip a full backup?
Hi,
I have a SqlExpress app and would like to start using differential
backups (we currently always use full backups) for our auto-backup
utility. The problem is that the users can create their own backups
(always full). My auto-backup utility will not use these files. I
control the auto-backups and hide them from the user.
Can I somehow tell SqlExpress to ignore any user-created backups and
reference my differentials off MY full backups?
thanks very much!
DeannaI see what you want to do, but unfortunately, there's no such setting.
If you could force your users to do the full backups using the COPY_ONLY option, then those full
backups would not affect your full backup/.diff backup sequence. But there's no such setting to
force a user to only do such backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165871674.022127.48890@.l12g2000cwl.googlegroups.com...
> Hi,
> I have a SqlExpress app and would like to start using differential
> backups (we currently always use full backups) for our auto-backup
> utility. The problem is that the users can create their own backups
> (always full). My auto-backup utility will not use these files. I
> control the auto-backups and hide them from the user.
> Can I somehow tell SqlExpress to ignore any user-created backups and
> reference my differentials off MY full backups?
> thanks very much!
> Deanna
>|||That's GREAT news. The user backs up via the application, so assuming
there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
research. If there's NOT and you have another way to programmatically
cause the COPY_ONLY option please let me know!
thanks so much!
Deanna|||> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free.
DMO is deprecated and does not expose new functionality in 2005.
If you really want to use DMO, use .ExecuteImmediate or similar method to execute the whole BACKUP
command.
Or, use the replacement SMO, which is managed API, and (should) expose the COPY_ONLY option of the
backup command through the object model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165928275.312674.220290@.f1g2000cwa.googlegroups.com...
> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
> research. If there's NOT and you have another way to programmatically
> cause the COPY_ONLY option please let me know!
> thanks so much!
> Deanna
>|||Tibor,
Thanks so much. I was just reading about SMO this morning (we JUST
moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
guess I should stick with DMO and use the .ExecuteImmediate. If anyone
knows a way to accomplish this with SMO please let me know. I'd prefer
to dump DMO.
Thanks for all the help!
Deanna|||> I do NOT see the COPY_ONLY
> exposed in SMO though.
Interesting. I had a look and couldn't find it. Perhaps it is considered too unusual so it isn't
exposed through the SQL object model.
> Nor do I see an .ExecuteImmediate on SMO.
I didn't say there exist such a method in SMO. I was referring to DMO. In SMO, you can use
ExecuteNonQuery on either a ServerConnection object (which is what I would use) or a Database
object.
Or, you check out the BackupRestoreBase class, which has members such as ExecuteSql (etc). I don't
think you'll find COPY_ONLY here, but it seems to be another way of sending TSQL commands to the
engine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message
news:1165933594.476139.251730@.l12g2000cwl.googlegroups.com...
> Tibor,
> Thanks so much. I was just reading about SMO this morning (we JUST
> moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
> exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
> guess I should stick with DMO and use the .ExecuteImmediate. If anyone
> knows a way to accomplish this with SMO please let me know. I'd prefer
> to dump DMO.
> Thanks for all the help!
> Deanna
>
I have a SqlExpress app and would like to start using differential
backups (we currently always use full backups) for our auto-backup
utility. The problem is that the users can create their own backups
(always full). My auto-backup utility will not use these files. I
control the auto-backups and hide them from the user.
Can I somehow tell SqlExpress to ignore any user-created backups and
reference my differentials off MY full backups?
thanks very much!
DeannaI see what you want to do, but unfortunately, there's no such setting.
If you could force your users to do the full backups using the COPY_ONLY option, then those full
backups would not affect your full backup/.diff backup sequence. But there's no such setting to
force a user to only do such backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165871674.022127.48890@.l12g2000cwl.googlegroups.com...
> Hi,
> I have a SqlExpress app and would like to start using differential
> backups (we currently always use full backups) for our auto-backup
> utility. The problem is that the users can create their own backups
> (always full). My auto-backup utility will not use these files. I
> control the auto-backups and hide them from the user.
> Can I somehow tell SqlExpress to ignore any user-created backups and
> reference my differentials off MY full backups?
> thanks very much!
> Deanna
>|||That's GREAT news. The user backs up via the application, so assuming
there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
research. If there's NOT and you have another way to programmatically
cause the COPY_ONLY option please let me know!
thanks so much!
Deanna|||> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free.
DMO is deprecated and does not expose new functionality in 2005.
If you really want to use DMO, use .ExecuteImmediate or similar method to execute the whole BACKUP
command.
Or, use the replacement SMO, which is managed API, and (should) expose the COPY_ONLY option of the
backup command through the object model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165928275.312674.220290@.f1g2000cwa.googlegroups.com...
> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
> research. If there's NOT and you have another way to programmatically
> cause the COPY_ONLY option please let me know!
> thanks so much!
> Deanna
>|||Tibor,
Thanks so much. I was just reading about SMO this morning (we JUST
moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
guess I should stick with DMO and use the .ExecuteImmediate. If anyone
knows a way to accomplish this with SMO please let me know. I'd prefer
to dump DMO.
Thanks for all the help!
Deanna|||> I do NOT see the COPY_ONLY
> exposed in SMO though.
Interesting. I had a look and couldn't find it. Perhaps it is considered too unusual so it isn't
exposed through the SQL object model.
> Nor do I see an .ExecuteImmediate on SMO.
I didn't say there exist such a method in SMO. I was referring to DMO. In SMO, you can use
ExecuteNonQuery on either a ServerConnection object (which is what I would use) or a Database
object.
Or, you check out the BackupRestoreBase class, which has members such as ExecuteSql (etc). I don't
think you'll find COPY_ONLY here, but it seems to be another way of sending TSQL commands to the
engine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message
news:1165933594.476139.251730@.l12g2000cwl.googlegroups.com...
> Tibor,
> Thanks so much. I was just reading about SMO this morning (we JUST
> moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
> exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
> guess I should stick with DMO and use the .ExecuteImmediate. If anyone
> knows a way to accomplish this with SMO please let me know. I'd prefer
> to dump DMO.
> Thanks for all the help!
> Deanna
>
Labels:
app,
auto-backup,
backup,
backups,
database,
differential,
differentials,
microsoft,
mysql,
oracle,
server,
skip,
sql,
sqlexpress,
utility
differentials question - can i skip a full backup?
Hi,
I have a SqlExpress app and would like to start using differential
backups (we currently always use full backups) for our auto-backup
utility. The problem is that the users can create their own backups
(always full). My auto-backup utility will not use these files. I
control the auto-backups and hide them from the user.
Can I somehow tell SqlExpress to ignore any user-created backups and
reference my differentials off MY full backups?
thanks very much!
DeannaI see what you want to do, but unfortunately, there's no such setting.
If you could force your users to do the full backups using the COPY_ONLY opt
ion, then those full
backups would not affect your full backup/.diff backup sequence. But there's
no such setting to
force a user to only do such backups...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165871674.022127.48890@.l12g2000cwl.googlegroup
s.com...
> Hi,
> I have a SqlExpress app and would like to start using differential
> backups (we currently always use full backups) for our auto-backup
> utility. The problem is that the users can create their own backups
> (always full). My auto-backup utility will not use these files. I
> control the auto-backups and hide them from the user.
> Can I somehow tell SqlExpress to ignore any user-created backups and
> reference my differentials off MY full backups?
> thanks very much!
> Deanna
>|||That's GREAT news. The user backs up via the application, so assuming
there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
research. If there's NOT and you have another way to programmatically
cause the COPY_ONLY option please let me know!
thanks so much!
Deanna|||> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free.
DMO is deprecated and does not expose new functionality in 2005.
If you really want to use DMO, use .ExecuteImmediate or similar method to ex
ecute the whole BACKUP
command.
Or, use the replacement SMO, which is managed API, and (should) expose the C
OPY_ONLY option of the
backup command through the object model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165928275.312674.220290@.f1g2000cwa.googlegroup
s.com...
> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
> research. If there's NOT and you have another way to programmatically
> cause the COPY_ONLY option please let me know!
> thanks so much!
> Deanna
>|||Tibor,
Thanks so much. I was just reading about SMO this morning (we JUST
moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
guess I should stick with DMO and use the .ExecuteImmediate. If anyone
knows a way to accomplish this with SMO please let me know. I'd prefer
to dump DMO.
Thanks for all the help!
Deanna|||> I do NOT see the COPY_ONLY
> exposed in SMO though.
Interesting. I had a look and couldn't find it. Perhaps it is considered to
o unusual so it isn't
exposed through the SQL object model.
> Nor do I see an .ExecuteImmediate on SMO.
I didn't say there exist such a method in SMO. I was referring to DMO. In SM
O, you can use
ExecuteNonQuery on either a ServerConnection object (which is what I would u
se) or a Database
object.
Or, you check out the BackupRestoreBase class, which has members such as Exe
cuteSql (etc). I don't
think you'll find COPY_ONLY here, but it seems to be another way of sending
TSQL commands to the
engine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message
news:1165933594.476139.251730@.l12g2000cwl.googlegroups.com...
> Tibor,
> Thanks so much. I was just reading about SMO this morning (we JUST
> moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
> exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
> guess I should stick with DMO and use the .ExecuteImmediate. If anyone
> knows a way to accomplish this with SMO please let me know. I'd prefer
> to dump DMO.
> Thanks for all the help!
> Deanna
>
I have a SqlExpress app and would like to start using differential
backups (we currently always use full backups) for our auto-backup
utility. The problem is that the users can create their own backups
(always full). My auto-backup utility will not use these files. I
control the auto-backups and hide them from the user.
Can I somehow tell SqlExpress to ignore any user-created backups and
reference my differentials off MY full backups?
thanks very much!
DeannaI see what you want to do, but unfortunately, there's no such setting.
If you could force your users to do the full backups using the COPY_ONLY opt
ion, then those full
backups would not affect your full backup/.diff backup sequence. But there's
no such setting to
force a user to only do such backups...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165871674.022127.48890@.l12g2000cwl.googlegroup
s.com...
> Hi,
> I have a SqlExpress app and would like to start using differential
> backups (we currently always use full backups) for our auto-backup
> utility. The problem is that the users can create their own backups
> (always full). My auto-backup utility will not use these files. I
> control the auto-backups and hide them from the user.
> Can I somehow tell SqlExpress to ignore any user-created backups and
> reference my differentials off MY full backups?
> thanks very much!
> Deanna
>|||That's GREAT news. The user backs up via the application, so assuming
there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
research. If there's NOT and you have another way to programmatically
cause the COPY_ONLY option please let me know!
thanks so much!
Deanna|||> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free.
DMO is deprecated and does not expose new functionality in 2005.
If you really want to use DMO, use .ExecuteImmediate or similar method to ex
ecute the whole BACKUP
command.
Or, use the replacement SMO, which is managed API, and (should) expose the C
OPY_ONLY option of the
backup command through the object model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165928275.312674.220290@.f1g2000cwa.googlegroup
s.com...
> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
> research. If there's NOT and you have another way to programmatically
> cause the COPY_ONLY option please let me know!
> thanks so much!
> Deanna
>|||Tibor,
Thanks so much. I was just reading about SMO this morning (we JUST
moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
guess I should stick with DMO and use the .ExecuteImmediate. If anyone
knows a way to accomplish this with SMO please let me know. I'd prefer
to dump DMO.
Thanks for all the help!
Deanna|||> I do NOT see the COPY_ONLY
> exposed in SMO though.
Interesting. I had a look and couldn't find it. Perhaps it is considered to
o unusual so it isn't
exposed through the SQL object model.
> Nor do I see an .ExecuteImmediate on SMO.
I didn't say there exist such a method in SMO. I was referring to DMO. In SM
O, you can use
ExecuteNonQuery on either a ServerConnection object (which is what I would u
se) or a Database
object.
Or, you check out the BackupRestoreBase class, which has members such as Exe
cuteSql (etc). I don't
think you'll find COPY_ONLY here, but it seems to be another way of sending
TSQL commands to the
engine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message
news:1165933594.476139.251730@.l12g2000cwl.googlegroups.com...
> Tibor,
> Thanks so much. I was just reading about SMO this morning (we JUST
> moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
> exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
> guess I should stick with DMO and use the .ExecuteImmediate. If anyone
> knows a way to accomplish this with SMO please let me know. I'd prefer
> to dump DMO.
> Thanks for all the help!
> Deanna
>
Labels:
app,
auto-backuputility,
backup,
backups,
database,
differentialbackups,
differentials,
microsoft,
mysql,
oracle,
server,
skip,
sql,
sqlexpress
differentials backups
Do differential backups have to be backed up into the same
file/device as the original backup? or can I make a
seperate file/device'not necessarily, you can create it on the seperate device as well.
--
- Vishal
"susan" <susanbauer@.yahoo.com> wrote in message
news:057801c37e23$7f417fe0$a301280a@.phx.gbl...
> Do differential backups have to be backed up into the same
> file/device as the original backup? or can I make a
> seperate file/device'
>
file/device as the original backup? or can I make a
seperate file/device'not necessarily, you can create it on the seperate device as well.
--
- Vishal
"susan" <susanbauer@.yahoo.com> wrote in message
news:057801c37e23$7f417fe0$a301280a@.phx.gbl...
> Do differential backups have to be backed up into the same
> file/device as the original backup? or can I make a
> seperate file/device'
>
Differential Vs. Full DB backup
My database is 30Gb in size. Full DB backup takes 30Gb and Differential
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon Shargorodsky
My guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon Shargorodsky
My guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
Differential Vs. Full DB backup
My database is 30Gb in size. Full DB backup takes 30Gb and Differential
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon ShargorodskyMy guess is that you have performed either reindexing or a shrink operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon ShargorodskyMy guess is that you have performed either reindexing or a shrink operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
Differential Vs. Full DB backup
My database is 30Gb in size. Full DB backup takes 30Gb and Differential
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon ShargorodskyMy guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon ShargorodskyMy guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
Differential SQL backups ? is this possible?
Hello, I am looking to backup one of our MS Sql databases offsite..
and because our databases are so large, and we do not want to consume
lots of bandwidth everynight during backups.. I was wondering:
Is it possible to backup only changes for a db?
thanks!
Lee
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict229803.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=795309
Have a look at transaction log and differential backups in BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
and because our databases are so large, and we do not want to consume
lots of bandwidth everynight during backups.. I was wondering:
Is it possible to backup only changes for a db?
thanks!
Lee
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict229803.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=795309
Have a look at transaction log and differential backups in BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Differential Restore
Win2K3 SQL2K Enterprise Edition
This is my current backup schedule
Sunday FULL
M-Sat Differential
TranLogs Backups every hour
I am trying to restore to a point in time, specifically to the time
right after my last Differentail backup. I am using Ent Manager for
this. I have restored the Full with no recovery and then I select just
the last Differential Backup for restore and I get the message
Cannot apply the backup on device 'd:\abcd\diff' to database 'TEST'
restore database is terminated abnormally
Is there something wrong with my restore process. This is a qa
environment that I am testing this on. I have added a lot of data to
the database and then manual run the diffential job. Now I am trying
to restore to this point but I keep getting this error. Any help would
be appreciated.Please check the backup file you are using.
Thanks
Ajay Rengunthwar|||Ajay, Not sure what you mean exactly. I was able to restore my last
full backup. Both with and without recovery. I am just having trouble
restoring the differentials backup after that.
This is my current backup schedule
Sunday FULL
M-Sat Differential
TranLogs Backups every hour
I am trying to restore to a point in time, specifically to the time
right after my last Differentail backup. I am using Ent Manager for
this. I have restored the Full with no recovery and then I select just
the last Differential Backup for restore and I get the message
Cannot apply the backup on device 'd:\abcd\diff' to database 'TEST'
restore database is terminated abnormally
Is there something wrong with my restore process. This is a qa
environment that I am testing this on. I have added a lot of data to
the database and then manual run the diffential job. Now I am trying
to restore to this point but I keep getting this error. Any help would
be appreciated.Please check the backup file you are using.
Thanks
Ajay Rengunthwar|||Ajay, Not sure what you mean exactly. I was able to restore my last
full backup. Both with and without recovery. I am just having trouble
restoring the differentials backup after that.
Labels:
backup,
backups,
current,
database,
differential,
differentialtranlogs,
editionthis,
enterprise,
fullm-sat,
houri,
microsoft,
mysql,
oracle,
restore,
schedulesunday,
server,
sql,
sql2k,
win2k3
Differential Restore
Win2K3 SQL2K Enterprise Edition
This is my current backup schedule
Sunday FULL
M-Sat Differential
TranLogs Backups every hour
I am trying to restore to a point in time, specifically to the time
right after my last Differentail backup. I am using Ent Manager for
this. I have restored the Full with no recovery and then I select just
the last Differential Backup for restore and I get the message
Cannot apply the backup on device 'd:\abcd\diff' to database 'TEST'
restore database is terminated abnormally
Is there something wrong with my restore process. This is a qa
environment that I am testing this on. I have added a lot of data to
the database and then manual run the diffential job. Now I am trying
to restore to this point but I keep getting this error. Any help would
be appreciated.Please check the backup file you are using.
Thanks
Ajay Rengunthwar|||Ajay, Not sure what you mean exactly. I was able to restore my last
full backup. Both with and without recovery. I am just having trouble
restoring the differentials backup after that.
This is my current backup schedule
Sunday FULL
M-Sat Differential
TranLogs Backups every hour
I am trying to restore to a point in time, specifically to the time
right after my last Differentail backup. I am using Ent Manager for
this. I have restored the Full with no recovery and then I select just
the last Differential Backup for restore and I get the message
Cannot apply the backup on device 'd:\abcd\diff' to database 'TEST'
restore database is terminated abnormally
Is there something wrong with my restore process. This is a qa
environment that I am testing this on. I have added a lot of data to
the database and then manual run the diffential job. Now I am trying
to restore to this point but I keep getting this error. Any help would
be appreciated.Please check the backup file you are using.
Thanks
Ajay Rengunthwar|||Ajay, Not sure what you mean exactly. I was able to restore my last
full backup. Both with and without recovery. I am just having trouble
restoring the differentials backup after that.
Differential restore
I can no longer restore a differential backup after
restoring the fullbackup. I found the microsoft work
around that says to do the restore throught the Query
Analizer but that doesn't work either. Does anyone know
how i cna fix this problem?This is a multi-part message in MIME format.
--=_NextPart_000_0372_01C3D5F8.5D023FA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Did you restore the full backup with NORECOVERY? If not, re-restore with
this option.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Timmerman" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...
I can no longer restore a differential backup after
restoring the fullbackup. I found the microsoft work
around that says to do the restore throught the Query
Analizer but that doesn't work either. Does anyone know
how i cna fix this problem?
--=_NextPart_000_0372_01C3D5F8.5D023FA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Did you restore the full backup with NORECOVERY? If not, re-restore with this option.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Timmerman" wrote in message news:0a0801c3d620$25=f35f30$a001280a@.phx.gbl...I can no longer restore a differential backup after restoring the =fullbackup. I found the microsoft work around that says to do the restore =throught the Query Analizer but that doesn't work either. Does anyone know =how i cna fix this problem?
--=_NextPart_000_0372_01C3D5F8.5D023FA0--|||Yes I did restored with norecovery. I also have SP3 loaded
and have tried to do the restore both through Enterprise
Manager and the Query Analyzer. Both worked until
recently. I have made no changes to the either the
configuration or the database. this is crazy
Thanks for any help you can give me.
jeff
>--Original Message--
>Did you restore the full backup with NORECOVERY? If not,
re-restore with
>this option.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jeff Timmerman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...
>I can no longer restore a differential backup after
>restoring the fullbackup. I found the microsoft work
>around that says to do the restore throught the Query
>Analizer but that doesn't work either. Does anyone know
>how i cna fix this problem?
>|||This is a multi-part message in MIME format.
--=_NextPart_000_017E_01C3D6A7.28C09CA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Just wondering about corruption here. Can you try backing up Northwind -
full and differential, plus a couple of txn logs, then restore them all? If
that fails, it could be a SQL Server problem. If it succeeds, it's your
original backups.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Timmerman" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca701c3d6cd$9aa7bdf0$a501280a@.phx.gbl...
Yes I did restored with norecovery. I also have SP3 loaded
and have tried to do the restore both through Enterprise
Manager and the Query Analyzer. Both worked until
recently. I have made no changes to the either the
configuration or the database. this is crazy
Thanks for any help you can give me.
jeff
>--Original Message--
>Did you restore the full backup with NORECOVERY? If not,
re-restore with
>this option.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jeff Timmerman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...
>I can no longer restore a differential backup after
>restoring the fullbackup. I found the microsoft work
>around that says to do the restore throught the Query
>Analizer but that doesn't work either. Does anyone know
>how i cna fix this problem?
>
--=_NextPart_000_017E_01C3D6A7.28C09CA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just wondering about corruption =here. Can you try backing up Northwind - full and differential, plus a couple of =txn logs, then restore them all? If that fails, it could be a SQL Server problem. If it succeeds, it's your original backups.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Timmerman" wrote in message news:0ca701c3d6cd$9a=a7bdf0$a501280a@.phx.gbl...Yes I did restored with norecovery. I also have SP3 loaded and have =tried to do the restore both through Enterprise Manager and the Query =Analyzer. Both worked until recently. I have made no changes to the =either the configuration or the database. this is crazyThanks for any =help you can give me.jeff>--Original Message-->Did =you restore the full backup with NORECOVERY? If not, re-restore with>this option.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Jeff= Timmerman" wrote in message>news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...>I =can no longer restore a differential backup after>restoring the =fullbackup. I found the microsoft work>around that says to do the restore =throught the Query>Analizer but that doesn't work either. Does anyone know>how i cna fix this problem?>
--=_NextPart_000_017E_01C3D6A7.28C09CA0--
restoring the fullbackup. I found the microsoft work
around that says to do the restore throught the Query
Analizer but that doesn't work either. Does anyone know
how i cna fix this problem?This is a multi-part message in MIME format.
--=_NextPart_000_0372_01C3D5F8.5D023FA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Did you restore the full backup with NORECOVERY? If not, re-restore with
this option.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Timmerman" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...
I can no longer restore a differential backup after
restoring the fullbackup. I found the microsoft work
around that says to do the restore throught the Query
Analizer but that doesn't work either. Does anyone know
how i cna fix this problem?
--=_NextPart_000_0372_01C3D5F8.5D023FA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Did you restore the full backup with NORECOVERY? If not, re-restore with this option.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Timmerman" wrote in message news:0a0801c3d620$25=f35f30$a001280a@.phx.gbl...I can no longer restore a differential backup after restoring the =fullbackup. I found the microsoft work around that says to do the restore =throught the Query Analizer but that doesn't work either. Does anyone know =how i cna fix this problem?
--=_NextPart_000_0372_01C3D5F8.5D023FA0--|||Yes I did restored with norecovery. I also have SP3 loaded
and have tried to do the restore both through Enterprise
Manager and the Query Analyzer. Both worked until
recently. I have made no changes to the either the
configuration or the database. this is crazy
Thanks for any help you can give me.
jeff
>--Original Message--
>Did you restore the full backup with NORECOVERY? If not,
re-restore with
>this option.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jeff Timmerman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...
>I can no longer restore a differential backup after
>restoring the fullbackup. I found the microsoft work
>around that says to do the restore throught the Query
>Analizer but that doesn't work either. Does anyone know
>how i cna fix this problem?
>|||This is a multi-part message in MIME format.
--=_NextPart_000_017E_01C3D6A7.28C09CA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Just wondering about corruption here. Can you try backing up Northwind -
full and differential, plus a couple of txn logs, then restore them all? If
that fails, it could be a SQL Server problem. If it succeeds, it's your
original backups.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Timmerman" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca701c3d6cd$9aa7bdf0$a501280a@.phx.gbl...
Yes I did restored with norecovery. I also have SP3 loaded
and have tried to do the restore both through Enterprise
Manager and the Query Analyzer. Both worked until
recently. I have made no changes to the either the
configuration or the database. this is crazy
Thanks for any help you can give me.
jeff
>--Original Message--
>Did you restore the full backup with NORECOVERY? If not,
re-restore with
>this option.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jeff Timmerman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...
>I can no longer restore a differential backup after
>restoring the fullbackup. I found the microsoft work
>around that says to do the restore throught the Query
>Analizer but that doesn't work either. Does anyone know
>how i cna fix this problem?
>
--=_NextPart_000_017E_01C3D6A7.28C09CA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just wondering about corruption =here. Can you try backing up Northwind - full and differential, plus a couple of =txn logs, then restore them all? If that fails, it could be a SQL Server problem. If it succeeds, it's your original backups.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Timmerman" wrote in message news:0ca701c3d6cd$9a=a7bdf0$a501280a@.phx.gbl...Yes I did restored with norecovery. I also have SP3 loaded and have =tried to do the restore both through Enterprise Manager and the Query =Analyzer. Both worked until recently. I have made no changes to the =either the configuration or the database. this is crazyThanks for any =help you can give me.jeff>--Original Message-->Did =you restore the full backup with NORECOVERY? If not, re-restore with>this option.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Jeff= Timmerman" wrote in message>news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...>I =can no longer restore a differential backup after>restoring the =fullbackup. I found the microsoft work>around that says to do the restore =throught the Query>Analizer but that doesn't work either. Does anyone know>how i cna fix this problem?>
--=_NextPart_000_017E_01C3D6A7.28C09CA0--
Differential maintenance in plan sql 2000: setup
Hello!
I'm trying to setup a differential maintenance plan to backup all
users databases on sql 2000 using t-sql (or not)
I can't figure out or find how to include "all user databases" like i
would using a maintenance plan. This is a cake to do in sql 2005 but
sql 2000.... well lets just say
There are maint plans in 2000, which you reach from the Maintenance Plans folder in EM. A 2000 maint
plan do not include diff backups, though.
Right-licking a db and select Backup and from there selecting Schedule do *not* create a maint plan.
So if you want to include stuff like naming the backup file with time stamp and deletion of old
backups, you have to do it yourself. There are plenty of code out there to get your started, for
instance below (2005, but adaptable for 2000):
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scottg" <scottgranado@.gmail.com> wrote in message
news:f9f653f3-f0c6-4cb8-ae3e-4694a380293f@.n58g2000hsf.googlegroups.com...
> Hello!
> I'm trying to setup a differential maintenance plan to backup all
> users databases on sql 2000 using t-sql (or not)
> I can't figure out or find how to include "all user databases" like i
> would using a maintenance plan. This is a cake to do in sql 2005 but
> sql 2000.... well lets just say
I'm trying to setup a differential maintenance plan to backup all
users databases on sql 2000 using t-sql (or not)
I can't figure out or find how to include "all user databases" like i
would using a maintenance plan. This is a cake to do in sql 2005 but
sql 2000.... well lets just say
There are maint plans in 2000, which you reach from the Maintenance Plans folder in EM. A 2000 maint
plan do not include diff backups, though.
Right-licking a db and select Backup and from there selecting Schedule do *not* create a maint plan.
So if you want to include stuff like naming the backup file with time stamp and deletion of old
backups, you have to do it yourself. There are plenty of code out there to get your started, for
instance below (2005, but adaptable for 2000):
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scottg" <scottgranado@.gmail.com> wrote in message
news:f9f653f3-f0c6-4cb8-ae3e-4694a380293f@.n58g2000hsf.googlegroups.com...
> Hello!
> I'm trying to setup a differential maintenance plan to backup all
> users databases on sql 2000 using t-sql (or not)
> I can't figure out or find how to include "all user databases" like i
> would using a maintenance plan. This is a cake to do in sql 2005 but
> sql 2000.... well lets just say
Differential maintenance in plan sql 2000: setup
Hello!
I'm trying to setup a differential maintenance plan to backup all
users databases on sql 2000 using t-sql (or not)
I can't figure out or find how to include "all user databases" like i
would using a maintenance plan. This is a cake to do in sql 2005 but
sql 2000.... well lets just say :(There are maint plans in 2000, which you reach from the Maintenance Plans folder in EM. A 2000 maint
plan do not include diff backups, though.
Right-licking a db and select Backup and from there selecting Schedule do *not* create a maint plan.
So if you want to include stuff like naming the backup file with time stamp and deletion of old
backups, you have to do it yourself. There are plenty of code out there to get your started, for
instance below (2005, but adaptable for 2000):
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scottg" <scottgranado@.gmail.com> wrote in message
news:f9f653f3-f0c6-4cb8-ae3e-4694a380293f@.n58g2000hsf.googlegroups.com...
> Hello!
> I'm trying to setup a differential maintenance plan to backup all
> users databases on sql 2000 using t-sql (or not)
> I can't figure out or find how to include "all user databases" like i
> would using a maintenance plan. This is a cake to do in sql 2005 but
> sql 2000.... well lets just say :(
I'm trying to setup a differential maintenance plan to backup all
users databases on sql 2000 using t-sql (or not)
I can't figure out or find how to include "all user databases" like i
would using a maintenance plan. This is a cake to do in sql 2005 but
sql 2000.... well lets just say :(There are maint plans in 2000, which you reach from the Maintenance Plans folder in EM. A 2000 maint
plan do not include diff backups, though.
Right-licking a db and select Backup and from there selecting Schedule do *not* create a maint plan.
So if you want to include stuff like naming the backup file with time stamp and deletion of old
backups, you have to do it yourself. There are plenty of code out there to get your started, for
instance below (2005, but adaptable for 2000):
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scottg" <scottgranado@.gmail.com> wrote in message
news:f9f653f3-f0c6-4cb8-ae3e-4694a380293f@.n58g2000hsf.googlegroups.com...
> Hello!
> I'm trying to setup a differential maintenance plan to backup all
> users databases on sql 2000 using t-sql (or not)
> I can't figure out or find how to include "all user databases" like i
> would using a maintenance plan. This is a cake to do in sql 2005 but
> sql 2000.... well lets just say :(
Subscribe to:
Posts (Atom)