April 18, 2008 at 2:37 am
Hi, this query is based on a sql 2000 box but the principles may be the same as as 2005 box.
Will extra full database backups taken ad-hoc through out the day (e.g to restore on a test server or another machine for whatever reason)
affect the ability to recover to point in time using an older backup and sequential logs.
Example:
At 1am on Monday morning I do a full database backup to tape.
There is a full log back up to a FP Server every half hour
Sometimes ad-hoc full backups are done on the database during the day to various locations. For the example say I do two ad-hoc backups on the Monday to a share on my machine, at 10am and 4.31pm.
On Tuesday my manager wants me to restore the database to 4.38pm on the previous Monday.
Again for example, I have cleaned my local machine so the two full database backups I did have been deleted.. I know that I could have restored the 4.31pm full backup and the 5pm Monday log to achieve my goal.
So this is where I have been told different things:
Can I restore a full backup using the 1am Monday backup retrieved from tape and then restore in the correct order all transactional log backups from the FP Server, i.e 1 full and 32 log backups???
Is this possible?
Do the extra backups have any effect on the point in time restoring options using transactional logs?
In theory could I go back 19 days to the beginning of the month and restore the 1am backup on the 1st April and all the other hundreds of logs in order since to achieve point in time recovery to the 19th April at 4.31pm
If indeed I can go back to any backup I want as long as I have all the logs in sequence then what events will reset the log sequence?
I was reading the altering the recovery model to "simple" will do this but anything else?
Thanks
April 18, 2008 at 2:49 am
- off course you cannot apply previous log-backups to the new full backup.
- every full backup starts a new recovery "cycle".
- For SQL2005 there is this new "copy_only" parameter that will prevent breaking your normal drp-scenario.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 18, 2008 at 3:01 am
A full backup will reset the differential base, meaning diff backups cannot be applied to an older full backup.
Neither full nor diff backups truncate the transaction log, so you should be able to restore from an older full backup using the trasnaction log backups that you have. This is providing you have no breaks in the log chain (truncating the log, changing recovery model) and all the lock backups are present
Please do test before you rely on this, as I have not had to do this myself.
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
April 18, 2008 at 3:05 am
Hi Mark,
The answer is yes, it's still possible to restore to a point in time when using the earlier backup. Naturally, you'd have to restore more log backups than if you'd kept the more recent full backup. All you need to restore to a point in time is an unbroken sequence of log backups starting with a full backup.
By the way, you don't want to switch to simple recovery mode. In simple recovery mode your log gets automatically truncated at regular intervals and you completely lose the ability to recover to a point in time. I'd only ever use simple recovery mode on development boxes or where there is absolutely no business need to recover to a point in time.
April 18, 2008 at 3:10 am
ALZDBA (4/18/2008)
- off course you cannot apply previous log-backups to the new full backup.- every full backup starts a new recovery "cycle".
- For SQL2005 there is this new "copy_only" parameter that will prevent breaking your normal drp-scenario.
Just to clear this up. I don't think Mark was asking if he could apply a previous log backup to the new backup. He was asking if he could start with the old full backup and restore logs that extended beyond the new full backup.
Correct me if I'm wrong.
April 18, 2008 at 3:35 am
that is correct,
I was always under the belief that all backups are linked to the latest full backup only. so any ad-hoc backups done would break the log chain and have to be the new starting point for PIT recovery. This of course could make DR very difficult because ad-hoc backups taken throughout the day could be stored anywhere and may not be backed up to tape for later recovery.
So to confirm my testing -- doing ad-hoc to file backups from the sql 2000 system during the day will not cause any disaster recovery problems, and any previous historical backup can be used as long as:
-- The full backup is successfully restored
-- All transaction logs are applied after the ealier backup in the correct order
-- no action was taken that could have broken the transaction sequence i.e changing to simple model and back to full model.
If this is the case and doing extra ad-hoc backups do not make a difference, what then is the advantage of the new "copy_only" feature in 2005?
Thanks
April 18, 2008 at 3:55 am
mark (4/18/2008)
If this is the case and doing extra ad-hoc backups do not make a difference, what then is the advantage of the new "copy_only" feature in 2005?
Normal full backups reset the differential base. So, if you take a full backup sunday, a diff backup monday, an ad-hoc full backup tuesday and another diff backup tuesday night, the second diff backup can only be restored onto the ad-hoc full taken on tuesday.
Taking a full with copy-only doesn't reset that differential base.
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
April 18, 2008 at 3:56 am
The little test ...
/*
use master
alter database SSC_TestBU set read_only with rollback immediate;
drop database SSC_TestBU;
alter database SSC_TestRcvr set read_only with rollback immediate;
drop database SSC_TestRcvr;
alter database SSC_TestRcvrPIT set read_only with rollback immediate;
drop database SSC_TestRcvrPIT;
*/
Create database SSC_TestBU;
go
ALTER DATABASE SSC_TestBU SET RECOVERY FULL ;
go
print convert(char(23), getdate(),121)
go
use SSC_TestBU;
go
create table T_test(idnr int identity(1,1) not null primary key, tsCreate datetime not null default getdate());
go
backup database SSC_TestBU to disk='x:\SSC_TestBUFull1.bak' with init;
go
print convert(char(23), getdate(),121)
go
declare @ctr as int
set @ctr = 0
while @ctr < 10
begin
Insert into T_Test default values;
set @ctr = @ctr + 1
end
Select * from T_Test order by tsCreate desc;
go
print convert(char(23), getdate(),121)
go
backup log SSC_TestBU to disk='x:\SSC_TestBULog1.bak' with init;
go
print convert(char(23), getdate(),121)
go
Insert into T_Test default values;
Select count(*), max(idnr) as max_idnr, max(tsCreate) as max_tsCreate
from T_Test ;
go
print convert(char(23), getdate(),121)
go
backup log SSC_TestBU to disk='x:\SSC_TestBULog2.bak' with init;
go
print convert(char(23), getdate(),121)
go
Insert into T_Test default values;
Insert into T_Test default values;
Select count(*), max(idnr) as max_idnr, max(tsCreate) as max_tsCreate
from T_Test ;
go
print convert(char(23), getdate(),121)
go
backup database SSC_TestBU to disk='x:\SSC_TestBUFull2.bak' with init;
go
print convert(char(23), getdate(),121)
go
go
Insert into T_Test default values;
Insert into T_Test default values;
Insert into T_Test default values;
Select count(*), max(idnr) as max_idnr, max(tsCreate) as max_tsCreate
from T_Test ;
go
backup log SSC_TestBU to disk='x:\SSC_TestBULog3.bak' with init;
go
Select * from SSC_TestBU..T_Test order by tsCreate desc;
go
checkpoint;
go
backup log SSC_TestBU to disk='x:\SSC_TestBULog4.bak' with init;
go
/*
use master
drop database SSC_TestRcvr;
*/
/* recovery */
print 'FULL restore : '
go
Restore database SSC_TestRcvr
from disk = N'x:\SSC_TestBUFull1.bak'
WITH MOVE N'SSC_TestBU' to 'X:\MSSQL$SQL2000PE\data\SSC_TestRcvr.mdf'
, MOVE N'SSC_TestBU_log' to 'X:\MSSQL$SQL2000PE\data\SSC_TestRcvr_log.LDF'
, norecovery ;
restore log SSC_TestRcvr
from disk='x:\SSC_TestBULog1.bak'
with norecovery;
restore log SSC_TestRcvr
from disk='x:\SSC_TestBULog2.bak'
with norecovery;
restore log SSC_TestRcvr
from disk='x:\SSC_TestBULog3.bak'
with recovery;
go
Select * from SSC_TestRcvr..T_Test order by tsCreate desc;
go
/*
use master
drop database SSC_TestRcvrPIT;
*/
/* PIT recovery */
declare @pit datetime
select top 1 @pit= dateadd(ms,2,tsCreate )
from (select top 2 tsCreate
from SSC_TestBU..T_Test
group by tsCreate
order by tsCreate desc ) a
order by tsCreate asc;
print 'to be restored up to : ' + convert(char(26),@PIT,121)
Restore database SSC_TestRcvrPIT
from disk = N'x:\SSC_TestBUFull1.bak'
WITH MOVE N'SSC_TestBU' to 'X:\MSSQL$SQL2000PE\data\SSC_TestRcvrPIT.mdf'
, MOVE N'SSC_TestBU_log' to 'X:\MSSQL$SQL2000PE\data\SSC_TestRcvrPIT_log.LDF'
, norecovery ;
restore log SSC_TestRcvrPIT
from disk='x:\SSC_TestBULog1.bak'
with norecovery
, stopat = @pit
;
restore log SSC_TestRcvrPIT
from disk='x:\SSC_TestBULog2.bak'
with norecovery
, stopat = @pit
;
restore log SSC_TestRcvrPIT
from disk='x:\SSC_TestBULog3.bak'
with norecovery
, stopat = @pit
;
-- this one may give an error because it's been taken after the initial pit-log-backup;
restore log SSC_TestRcvrPIT
from disk='x:\SSC_TestBULog4.bak'
with recovery
, stopat = @pit
;
go
restore log SSC_TestRcvrPIT
with recovery
;
go
Select * from SSC_TestRcvrPIT..T_Test order by tsCreate desc;
go
-- CLEAN IT UP !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 18, 2008 at 4:03 am
Put it this way, if log backups only worked off the most recent full backup then log shipping would never work if you ever did a full backup of your primary database every night. In log shipping the transaction logs you are restoring are a sequence of log backups that could have started with a backup taken months ago.
April 18, 2008 at 4:07 am
So Gail, if I understand what you are saying:
If differential sql backups were part of my plan then doing ad-hoc backups will create a new starting point for the differential.
So Tuesdays nights differential will link to Mondays nights full tape backup UNLESS an ad-hoc backup is done during Monday -- therefore the ad-hoc becomes the base or starting point for the differential restore.
If I do not use differential backups as part of my plan then nothing is reset. just the inconvenience of having to restore a lot more log files (and the risk of having one missed etc).
I am getting clearer now.
We back up and restore database throughout the day to the reporting server. I was confused in thinking dthat oing these backups could prevent database retrieval off tape and logs if recovery was required
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply