November 28, 2012 at 2:16 am
Hello,
I've started out testing how a database using the full recovery model is backed up starting with a full database backup and subsequent regular log backups and believe I understand the basic principle.
i.e the full DB backup is the basis or foundation of the backup chain and the subsequent log backups form the linked chain allowing point-in-time recovery to any period falling within that chain.
So, my questions are:
1. Why perform a differential database backup as opposed to another log backup?
2. Why ever destroy the existing chain by performing a second full backup other than for purely space or management reasons?
Regards
Steve
November 28, 2012 at 2:42 am
1) Space Issue
2) Imagine if you have 100 of transactional log backup after full backup.Now if you want to restore database or in case of disaster ,you need to apply all the 100 transactional log backup files after full backup ,that time consuming ,
thats why frequently full and differential backups
-----------------------------------------------------------------------------
संकेत कोकणे
November 28, 2012 at 2:44 am
raotor (11/28/2012)
1. Why perform a differential database backup as opposed to another log backup?
Speed of restores. When restoring a diff, you just need the latest one. When restoring logs, you need to restore all log backups since the full/diff.
It can be the difference between restoring 1 full, 1 diff and 10 log backups vs 1 full and 40 log backups for eg.
2. Why ever destroy the existing chain by performing a second full backup other than for purely space or management reasons?
Full backups don't break the log chain, so the question is moot.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2012 at 2:58 am
To add a bit about the second question. At Pass summit 2011 Paul Randle had a presentation that he told about a bank that had one old full backup that they've done and then thousands of log backups that were done for few years (yes years, this is not a type nor a mistake). After few years without full backup or differential backup they had to do restore. They found themselves restoring few thousands log backups. Amazingly all the files still existed and were in good condition, so the chain didn't break. Unfortunately the whole process of restore took them 3 days. During that time they were close for business.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 28, 2012 at 3:02 am
Adi Cohn-120898 (11/28/2012)
Unfortunately the whole process of restore took them 3 days. During that time they were close for business.
Amazing that they reopened afterwards. When I worked for a bank, 3 days downtime (no business at all) and we probably wouldn't have had a business to recover.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2012 at 3:05 am
After a short time (don't remember if he said how long), they ran out of business.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 28, 2012 at 5:22 am
raotor (11/28/2012)
2. Why ever destroy the existing chain by performing a second full backup other than for purely space or management reasons?
Full backups DO reset the Differential Base LSN, so the only chain they break is for differential backups not for transaction logs.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 28, 2012 at 11:58 am
Perry Whittle (11/28/2012)
raotor (11/28/2012)
2. Why ever destroy the existing chain by performing a second full backup other than for purely space or management reasons?Full backups DO reset the Differential Base LSN, so the only chain they break is for differential backups not for transaction logs.
I have a test scenario running where I'd performed an initial full backup last week and then performed a log backup every hour.
Today I performed a second full database backup after thinking I wouldn't destroy the chain and now I cannot restore the database to a point in time before the most recently performed full backup done today, so hasn't the chain been broken if that's the case?
November 28, 2012 at 12:04 pm
what is the error you are receiving?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 28, 2012 at 12:06 pm
raotor (11/28/2012)
Today I performed a second full database backup after thinking I wouldn't destroy the chain and now I cannot restore the database to a point in time before the most recently performed full backup done today, so hasn't the chain been broken if that's the case?
Are you using the correct full backup? (the one taken last week)?
Has the recovery model been changed at any point since that full backup last week?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2012 at 12:36 pm
Perry Whittle (11/28/2012)
what is the error you are receiving?
There's no error as such.
When I right-click on the database, click Tasks, click Restore and then select Database the list of available Full/Log backups appears, but the first in that list is now the most recent full backup followed by the rest of today's log backups.
I am not presented with any of the previous log backups and original full backup.
November 28, 2012 at 12:41 pm
The problem is that you're using the GUI. Don't.
Script your restores (there are scripts that will create the restore statements for you from MSDB). There's no broken log chain here, there's just a broken GUI.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2012 at 12:42 pm
GilaMonster (11/28/2012)
raotor (11/28/2012)
Today I performed a second full database backup after thinking I wouldn't destroy the chain and now I cannot restore the database to a point in time before the most recently performed full backup done today, so hasn't the chain been broken if that's the case?Are you using the correct full backup? (the one taken last week)?
Has the recovery model been changed at any point since that full backup last week?
My full backups are done to the same file (i use NOINIT option on BACKUP), so the same physical backup file contains both full backups as far as I understand. In fact.
DB model has not changed.
November 28, 2012 at 12:51 pm
Ahhh, well at least it's the GUI and not me for a change 🙂
Thanks.
November 28, 2012 at 12:55 pm
raotor (11/28/2012)
My full backups are done to the same file (i use NOINIT option on BACKUP), so the same physical backup file contains both full backups as far as I understand.
Don't do that. Each backup should go to its own file, preferably with the name including the date and time the backup was taken.
With multiple backups in the same file, should the backup header be corrupted or should someone accidentally delete the file, you've just lost all your backups, not one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply