November 12, 2008 at 1:10 pm
I have searched the web and cannot find succinct answers to the following questions:
1. Does a full backup in SQL Server 2000 remove inactive entries from the current transaction log or is this done by the first transaction log backup after the full backup?
2. Will performing an ad-hoc full backup in the middle of a production full / transaction log backup sequence invalidate the log chain or can the ad-hoc backup be de-selected and ignored during a restore?
November 12, 2008 at 1:16 pm
Phil Auer (11/12/2008)
I have searched the web and cannot find succinct answers to the following questions:1. Does a full backup in SQL Server 2000 remove inactive entries from the current transaction log or is this done by the first transaction log backup after the full backup?
2. Will performing an ad-hoc full backup in the middle of a production full / transaction log backup sequence invalidate the log chain or can the ad-hoc backup be de-selected and ignored during a restore?
1) Full and Differential backups do not remove inactive entries from the transaction log. This is only accomplished by a transaction log backup.
2) Performing an ad-hoc full backup does not break the log chain. If, for instance, the ad-hoc backup were to be deleted, you can take the previous full backup and apply all the transaction log backups taken after the restored full backup and recover to a point after the ad-hoc full backup if you have those transaction log files as well.
Answer your questions?
November 12, 2008 at 1:39 pm
Thank you very much for the on-point answers.
Two follow-ups:
1. Is removing the inactive entries on a full backup an option (which I would want to NOT select) in SQL Server 2005? I thought I read this somewhere.
2. I do not have another SQL Server 2000 instance to test on (all others are 2005) and I don't want to play around on my production server. Under my scenario of doing the ad-hoc full backup, if I did a restore using Enterprise Manager instead of TSQL commands, will it allow me de-select the ad-hoc full backup?
November 12, 2008 at 1:45 pm
Phil Auer (11/12/2008)
Thank you very much for the on-point answers.1. Is removing the inactive entries on a full backup an option (which I would want to NOT select) in SQL Server 2005? I thought I read this somewhere.
No. Full backups have never truncated the transaction log. It's an option (that you do want to select) on transaction log backups
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 12, 2008 at 1:49 pm
Phil Auer (11/12/2008)
Thank you very much for the on-point answers.Two follow-ups:
1. Is removing the inactive entries on a full backup an option (which I would want to NOT select) in SQL Server 2005? I thought I read this somewhere.
2. I do not have another SQL Server 2000 instance to test on (all others are 2005) and I don't want to play around on my production server. Under my scenario of doing the ad-hoc full backup, if I did a restore using Enterprise Manager instead of TSQL commands, will it allow me de-select the ad-hoc full backup?
1) I agree with Gail on this one.
2) Not having used EM for over 3 years, I can't answer this one.
November 18, 2008 at 10:08 am
Phil Auer (11/12/2008)
2. I do not have another SQL Server 2000 instance to test on (all others are 2005) and I don't want to play around on my production server. Under my scenario of doing the ad-hoc full backup, if I did a restore using Enterprise Manager instead of TSQL commands, will it allow me de-select the ad-hoc full backup?
I think a list box comes up and you can deselect the ad-hoc full, and select/deselect the t-logs
December 3, 2008 at 10:07 am
I thought I was beginning to understand the nuances of backups / LSNs until I stumbled upon this video:
http://www.sqlservercentral.com/articles/Video/63811/
It talks about the pitfalls of running ad-hoc backups and, if I understand correctly, says that it will basically render the previous full backup unusable.
It was my understanding that I could just skip the ad-hoc backup.
December 3, 2008 at 1:51 pm
Phil Auer (12/3/2008)
It talks about the pitfalls of running ad-hoc backups and, if I understand correctly, says that it will basically render the previous full backup unusable.
Adhoc fulls don't render previous full backups unusable. What they do is reset the differential base, so that subsequent diff backups are based off the adhoc full backup, not the scheduled full backup.
It can make a recovery difficult or impossible if the admin isn't aware of the adhoc backup or if it can't be found.
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
December 4, 2008 at 4:49 am
I'm still not clear on the issue. This video talks specifically about only full backups and transaction log backups.
December 4, 2008 at 8:00 am
Phil Auer (12/4/2008)
I'm still not clear on the issue. This video talks specifically about only full backups and transaction log backups.
I'll download the video later, watch it and see if I can clear the issue up.
Edit: Read the discussion page on the video.
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
December 4, 2008 at 9:13 am
Full backups do not break the LSN chain.
Easy to demonstrate
Create database TestingLogs
Go
use TestingLogs
go
Create Table Test (id int)
insert into Test values (1)
go
backup database TestingLogs to disk = 'D:\Develop\TestingFull1.bak' -- full backup 1
go
insert into Test values (2)
go
backup log TestingLogs to disk = 'D:\Develop\TestingLog1.trn' -- log 1
go
insert into Test values (3)
go
backup database TestingLogs to disk = 'D:\Develop\TestingFull2.bak' -- full backup 2
go
insert into Test values (4)
go
backup log TestingLogs to disk = 'D:\Develop\TestingLog2.trn' -- log 2
go
use master
go
drop database TestingLogs -- simulate a disaster
go
restore database TestingLogs from disk = 'D:\Develop\TestingFull1.bak' with norecovery -- restore full 1
restore log TestingLogs from disk = 'D:\Develop\TestingLog1.trn' with norecovery -- and log backup 1
restore log TestingLogs from disk = 'D:\Develop\TestingLog2.trn' with norecovery -- and log backup 2
restore database TestingLogs with recovery -- and bring it online
select * from TestingLogs.dbo.Test -- 4 rows returned
Output from the restores:
The full DB restore
Processed 184 pages for database 'TestingLogs', file 'TestingLogs' on file 1.
Processed 5 pages for database 'TestingLogs', file 'TestingLogs_log' on file 1.
RESTORE DATABASE successfully processed 189 pages in 0.073 seconds (20.180 MB/sec).
The first of the log restores
Processed 0 pages for database 'TestingLogs', file 'TestingLogs' on file 1.
Processed 6 pages for database 'TestingLogs', file 'TestingLogs_log' on file 1.
RESTORE LOG successfully processed 6 pages in 0.004 seconds (10.009 MB/sec).
The second of the log restores
Processed 0 pages for database 'TestingLogs', file 'TestingLogs' on file 1.
Processed 2 pages for database 'TestingLogs', file 'TestingLogs_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.004 seconds (3.417 MB/sec).
Bringing the DB online
RESTORE DATABASE successfully processed 0 pages in 0.237 seconds (0.000 MB/sec).
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
December 4, 2008 at 9:34 am
Response copied from the discussion on the backup:
Sorry, I should have clarified more how this works. This was intended to show off Copy Only backups, and not cover all aspects of backups.
Full backups do not break the LSN chain. I misspoke if that's in there. I'll check and try to re-shoot this if it is. If the LSN broke, then log shipping would require full backups to move over.
However what typically happens is that the last full backup or two are all that are kept, and logs backups in between are often deleted by automated processes (maintenance plans and home grown solutions) so as to save space. Having extra backups occurring means that you might not be aware of which backups to restore with which logs. Especially if they are not in the same folders.
Or you have people writing backups to the same device or file, which means that you have a new starting point for recovery.
A full backup lowers the time it takes to recover if there is an issue. And it lowers risk. Every extra log you need to restore means that is one more file you have to ensure is intact. If you are going back to the full from two night's ago to do a restore, you need to be sure not only that you have every log file since that backup, but also that they are readable, no media errors, etc.
You also have the issue of differential backups, which are taken to speed recovery as well. The problems with a regular backup is they can invalidate a differential, which can cause the admin issues.
The more I think about it, the more I think I've misspoken in the video since I viewed it as an admin having issues with backup chains because older logs might not be available. I'll remove the video for the time being until I can clarify the workings of the backup process.
December 4, 2008 at 10:32 am
OK, Thanks. The devil is in the details in the whole backup / recovery area and I am trying to get my DR procedures down before I ever have to use them.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply