Our production backup schedule is, FULL backup once a month and a DIFFERENTIAL backup every day.
We are starting the FULL and DIFFERENTIAL backups using something similar to
FULL Backup
backup database @.DB to disk = @.BackupFile
DIFFERENTIAL backup
backup database @.DB to disk = @.BackupFile WITH DIFFERENTIAL
A full backup was done on 10/6 and the DB size was 48GB. Since then we have been doing DIFFERENTIAL backups. I recently looked at the DIFFERENTIAL backup directory and found something I think is interesting.
10/07/2005 04:00 AM 8,604,160 prodDB_Diff_200510070400.BAK
10/08/2005 04:00 AM 1,144,320 prodDB_Diff_200510080400.BAK
10/09/2005 04:00 AM 1,134,080 prodDB_Diff_200510090400.BAK
10/10/2005 04:00 AM 21,185,024 prodDB_Diff_200510100400.BAK
10/11/2005 04:00 AM 7,119,360 prodDB_Diff_200510110400.BAK
10/12/2005 04:00 AM 163,669,504 prodDB_Diff_200510120400.BAK
10/13/2005 04:00 AM 14,743,040 prodDB_Diff_200510130400.BAK
10/14/2005 04:00 AM 120,875,520 prodDB_Diff_200510140400.BAK
10/15/2005 04:00 AM 1,216,000 prodDB_Diff_200510150400.BAK
10/16/2005 04:00 AM 5,139,968 prodDB_Diff_200510160400.BAK
10/17/2005 04:00 AM 4,277,760 prodDB_Diff_200510170400.BAK
10/18/2005 04:00 AM 2,778,624 prodDB_Diff_200510180400.BAK
10/19/2005 04:01 AM 750,575,104 prodDB_Diff_200510190400.BAK
My understanding of DIFFERENTIAL backups from books online is
"Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"
MY Question:
Assuming I am understanding this correctly, all DIFFERENTIAL backups are independent of one another. If a failure happens, all I have to do is restore the latest FULL backup and then
restore the latest DIFFERENTIAL backup.
So, I would think the sizes of the backup files should be in ASCENDING order. Why is the size of the backup file from 10/15/2005 (1,216,000 bytes) smaller than the backup file from 10/14/2005 (120,875,520 bytes).
Assuming a crash happens on 10/15 5AM, I would restore the full backup from 10/6 and then restore the DIFF. backup from 10/15 4AM (Which will apply approx.. 1,216,000 bytes of data). So where has the data in the backup file from 10/14/2005 gone (120,875,520 bytes)?
ThanksНou mistaken
Look for keyword "Differential Database Backups"
"A differential database backup records only the data that has changed since the last database backup."
Draw attention - "last database backup". No full backup.|||From BOL:
DIFFERENTIAL
Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup. A differential backup usually takes up less space than a full backup. Use this option so that all individual log backups since the last full backup do not need to be applied. For more information, see Differential Database Backups and File Differential Backups.
Note During a full database or differential backup, SQL Server backs up enough of the transaction log to produce a consistent database when the database is restored.
Are you doing a lot of updates during the time it takes to do the backups?|||Thanks so much for your response..
Right, your quote from BOL is exactly what I read. It has FULL backup written all over it.
Are you sure it is
""A differential database backup records only the data that has changed since the last database backup."
rather than what BOL says it is
"Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"
I wouldn't say lots of updates but the DB is being used.|||You have the right idea about restoring the database in your situation. Just 2 restores, and one recovery, no matter what day you go down.
I am also puzzled by the wild size differences in there. This isn't something strange like the destination of a log-shipping instance, is it? I could see full backups on the source database causing this behavior.
Showing posts with label schedule. Show all posts
Showing posts with label schedule. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
Question about catalog schedule
When I start a full population on a full_text catalog manually (Right click
on a full_text catalog and click start full_text population), it works
perfectly. But when I try to schedule a full pupulation on the same catalog,
it seems like it never start to do the full population. Do I need to set up
something in sql server inorder to invoke the event?
Thanks you.
no, you should not have to do anything special.
Check the gatherer logs to see if the population happend. You can find them
in
C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\GatherLogs
Use gthrlog.vbs to interpret them. You can find this in:
C:\Program Files\Common Files\System\MSSearch\Bin
Copy it to %windir%\system32 and you can run this script anywhere.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:D7BFE12F-8A58-417F-AC5B-DA33792ADAB3@.microsoft.com...
> When I start a full population on a full_text catalog manually (Right
click
> on a full_text catalog and click start full_text population), it works
> perfectly. But when I try to schedule a full pupulation on the same
catalog,
> it seems like it never start to do the full population. Do I need to set
up
> something in sql server inorder to invoke the event?
> Thanks you.
|||Tom,
Could you review your server's Application event log for "Microsoft Search"
and MssCi source events? Both of these events record the success and failure
of an attempted Full Population. If there are any errors near the time that
you attempted a scheduled SQLServerAgent job, could you post the event text?
Thanks,
John
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:D7BFE12F-8A58-417F-AC5B-DA33792ADAB3@.microsoft.com...
> When I start a full population on a full_text catalog manually (Right
click
> on a full_text catalog and click start full_text population), it works
> perfectly. But when I try to schedule a full pupulation on the same
catalog,
> it seems like it never start to do the full population. Do I need to set
up
> something in sql server inorder to invoke the event?
> Thanks you.
on a full_text catalog and click start full_text population), it works
perfectly. But when I try to schedule a full pupulation on the same catalog,
it seems like it never start to do the full population. Do I need to set up
something in sql server inorder to invoke the event?
Thanks you.
no, you should not have to do anything special.
Check the gatherer logs to see if the population happend. You can find them
in
C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\GatherLogs
Use gthrlog.vbs to interpret them. You can find this in:
C:\Program Files\Common Files\System\MSSearch\Bin
Copy it to %windir%\system32 and you can run this script anywhere.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:D7BFE12F-8A58-417F-AC5B-DA33792ADAB3@.microsoft.com...
> When I start a full population on a full_text catalog manually (Right
click
> on a full_text catalog and click start full_text population), it works
> perfectly. But when I try to schedule a full pupulation on the same
catalog,
> it seems like it never start to do the full population. Do I need to set
up
> something in sql server inorder to invoke the event?
> Thanks you.
|||Tom,
Could you review your server's Application event log for "Microsoft Search"
and MssCi source events? Both of these events record the success and failure
of an attempted Full Population. If there are any errors near the time that
you attempted a scheduled SQLServerAgent job, could you post the event text?
Thanks,
John
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:D7BFE12F-8A58-417F-AC5B-DA33792ADAB3@.microsoft.com...
> When I start a full population on a full_text catalog manually (Right
click
> on a full_text catalog and click start full_text population), it works
> perfectly. But when I try to schedule a full pupulation on the same
catalog,
> it seems like it never start to do the full population. Do I need to set
up
> something in sql server inorder to invoke the event?
> Thanks you.
Wednesday, March 7, 2012
Question about backing up translog vs. full backup
When doing a scheduled "full" backup, does it automatically remove inactive
entries from the translog, or do I need to schedule a separate backup just
for the translog? I am using Sql 2005. Thanks!You'll need to backup the log also (this makes sense if you think of
log-shipping which requires the logs intact and which might be occurring
behind the scenes).
Alternatively, if the log is of no importance (eg on some of my dev
databases) you might want to consider simple recovery mode.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Dan E wrote:
> When doing a scheduled "full" backup, does it automatically remove inactive
> entries from the translog, or do I need to schedule a separate backup just
> for the translog? I am using Sql 2005. Thanks!
>
That depends on the recovery model you're using. In Simple mode, the
transaction log is purged with every checkpoint, you don't need to do
anything. In Full or Bulk-Logged mode, you need to backup the
transaction log in order to purge it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
entries from the translog, or do I need to schedule a separate backup just
for the translog? I am using Sql 2005. Thanks!You'll need to backup the log also (this makes sense if you think of
log-shipping which requires the logs intact and which might be occurring
behind the scenes).
Alternatively, if the log is of no importance (eg on some of my dev
databases) you might want to consider simple recovery mode.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Dan E wrote:
> When doing a scheduled "full" backup, does it automatically remove inactive
> entries from the translog, or do I need to schedule a separate backup just
> for the translog? I am using Sql 2005. Thanks!
>
That depends on the recovery model you're using. In Simple mode, the
transaction log is purged with every checkpoint, you don't need to do
anything. In Full or Bulk-Logged mode, you need to backup the
transaction log in order to purge it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Question about backing up translog vs. full backup
When doing a scheduled "full" backup, does it automatically remove inactive
entries from the translog, or do I need to schedule a separate backup just
for the translog? I am using Sql 2005. Thanks!You'll need to backup the log also (this makes sense if you think of
log-shipping which requires the logs intact and which might be occurring
behind the scenes).
Alternatively, if the log is of no importance (eg on some of my dev
databases) you might want to consider simple recovery mode.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Dan E wrote:
> When doing a scheduled "full" backup, does it automatically remove inactiv
e
> entries from the translog, or do I need to schedule a separate backup just
> for the translog? I am using Sql 2005. Thanks!
>
That depends on the recovery model you're using. In Simple mode, the
transaction log is purged with every checkpoint, you don't need to do
anything. In Full or Bulk-Logged mode, you need to backup the
transaction log in order to purge it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
entries from the translog, or do I need to schedule a separate backup just
for the translog? I am using Sql 2005. Thanks!You'll need to backup the log also (this makes sense if you think of
log-shipping which requires the logs intact and which might be occurring
behind the scenes).
Alternatively, if the log is of no importance (eg on some of my dev
databases) you might want to consider simple recovery mode.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Dan E wrote:
> When doing a scheduled "full" backup, does it automatically remove inactiv
e
> entries from the translog, or do I need to schedule a separate backup just
> for the translog? I am using Sql 2005. Thanks!
>
That depends on the recovery model you're using. In Simple mode, the
transaction log is purged with every checkpoint, you don't need to do
anything. In Full or Bulk-Logged mode, you need to backup the
transaction log in order to purge it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Subscribe to:
Posts (Atom)