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 differential. Show all posts
Showing posts with label differential. Show all posts
Monday, March 12, 2012
Wednesday, March 7, 2012
Question about backups.
Hi guys,
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
Thanks
You always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks
|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FULL
> backup. Be aware that if you are using the SIMPLE recovery mode or you are
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>
|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:
|||Thanks a lot. I appreciate all your help.
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
Thanks
You always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks
|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FULL
> backup. Be aware that if you are using the SIMPLE recovery mode or you are
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>
|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:
|||Thanks a lot. I appreciate all your help.
Question about backups.
Hi guys,
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
ThanksYou always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FUL
L
> backup. Be aware that if you are using the SIMPLE recovery mode or you ar
e
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:|||Thanks a lot. I appreciate all your help.
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
ThanksYou always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FUL
L
> backup. Be aware that if you are using the SIMPLE recovery mode or you ar
e
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:|||Thanks a lot. I appreciate all your help.
Question about backups.
Hi guys,
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
ThanksYou always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
--
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FULL
> backup. Be aware that if you are using the SIMPLE recovery mode or you are
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
--
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:
>> You always have to restore the last FULL backup and then you should only
>> need the last DIFF since it always includes any changes since the last
>> FULL backup. Be aware that if you are using the SIMPLE recovery mode or
>> you are not issuing regular LOG backups you will not be able to recover
>> any transactions that occurred since the last DIFF and the time you have
>> an issue.|||Thanks a lot. I appreciate all your help.
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
ThanksYou always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
--
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FULL
> backup. Be aware that if you are using the SIMPLE recovery mode or you are
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
--
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:
>> You always have to restore the last FULL backup and then you should only
>> need the last DIFF since it always includes any changes since the last
>> FULL backup. Be aware that if you are using the SIMPLE recovery mode or
>> you are not issuing regular LOG backups you will not be able to recover
>> any transactions that occurred since the last DIFF and the time you have
>> an issue.|||Thanks a lot. I appreciate all your help.
Subscribe to:
Posts (Atom)