Point in time restore with differential backups

Question

Point in time restore with differential backups

mngong

SSCertifiable

Points: 6167
More actions
June 5, 2007 at 9:12 am

#117336

Is it possible to do a point in time restore with differential backups?
I failed consistently in my tests and want to make sure I am not missing anything
Your response is appreciated ...
Mike

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

No. Restoring to a point in time requires transaction log backups. Restoring from a differential backup can only be done to the time the differential backup was made.

Greg

mngong SSCertifiable Points: 6167 More actions · Answer 2

mngong

SSCertifiable

Points: 6167

June 5, 2007 at 9:35 am

#710870

Thanks Greg

Mike

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

Actually the answer is not 100% correct. You can do point in time with differential backups, provided that you also have transaction log backups covering the diff to point in time period.

Thus the use of differential backups does not prevent a point in time restore.

http://technet.microsoft.com/en-us/library/ms178105.aspx

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 4

In Sql 2005 u can perform point in time recovery with full backups but in sql 2000 u need tran log for performing point in time recovery

Rgds

Deepak

[font="Verdana"]- Deepak[/font]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Jeff,

Sorry if my answer wasn't clear. I wasn't trying to imply that restoring a differential backup prevents point-in-time recovery, only that log backups are also needed.

Deepak,

Point-in-time recovery in SQL 2005 is not possible with only full backups. Log backups are also needed, just like in SQL 2000.

Greg

Jeff Gray SSChampion Points: 10667 More actions · Answer 6

Assuredly so!

I was just trying to clarify for our OP that indicated he was having problems passing tests.

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 7

hi greg,

Assume u create a table in a db called mydb @1:50 am.........

insert 5 values @ 1:55 am into the table ..........insert 10 values @ 2:05 am....

now take a full backup of the entire db @2:10 am.........then restore the db with stopat ='june 08, 2007 2:00'.....then run a select query in the table at the newly restored db......u will get only 5 values from the table.......

rgds

Deepak

[font="Verdana"]- Deepak[/font]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 8

Hi,

I tried that and could not restore successfully. I inserted at 9:58 and again at 10:02. I did a full backup at 10:03 and when I tried to restore to 10:00, I got this message:

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

Greg

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 9

hi dude,

u shud commit whatever transaction u perform......then try out

[font="Verdana"]- Deepak[/font]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 10

You mean the inserts? They were committed before I made the backup. I wasn't able to restore to a point in time from the full backup.

Greg

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 11

Hi Greg,

pls refer the below link,

http://blogs.msdn.com/arvindsh/archive/2007/06/15/the-role-of-stopat-in-restore-database.aspx

[font="Verdana"]- Deepak[/font]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 12

What that blog entry says is that you can't actually recover to a point-in-time from a full backup only. The purpose of using STOPAT with RECOVER DATABASE is to see if the full backup was made before the point-in-time you want to recover to so you'll know if you are starting with the correct backup.

As the author says, "Without log backups, there is simply no way to achieve a true point-in-time restore"

Greg

Paul Williams SSCommitted Points: 1930 More actions · Answer 13

I agree.

I read the article too after following this post and it states that you CANNOT recover to a point - in - time after doing FULL or DIFFERENTIAL backups.

The articles states as Greg suggested, in that it is merely useful in determining whether the backup was made before the point-in-time you wish to restore too.

I haven't tried Deepaks suggested T-SQL though so it's interesting to note whether anyone has obtained the solution he suggests.

Paul R Williams.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 14

Books Online very clearly states:

The target recovery point must be contained in a transaction log backup.

See this topic in Books Online for the above quote: restoring databases [SQL Server], point in time

I didn't look at anyone's sample code, but I tried the following code of my own, and it shows that it restored the full backup to the end point:

Create

Table dbo.TestBak (TestID int identity(1, 1) not null primary key,

TestValue

varchar(5) null)

Go

Declare

@Time1 datetime, @Time2 datetime, @Time3 datetime

Begin

Tran

Insert Into dbo.TestBak (TestValue)

Select 'aaaaa'

Union All

Select 'bbbbb'

Union All

Select 'ccccc'

Union All

Select 'ddddd'

Union All

Select 'eeeee'

Commit

Tran

Set

@Time1 = getdate();

Select

*

From

dbo.TestBak;

Waitfor

delay '00:02:00';

Set

@Time2 = getdate();

Waitfor

delay '00:02:00';

Set

@Time3 = getdate();

Begin

Tran

Insert Into dbo.TestBak (TestValue)

Select 'fffff'

Union All

Select 'ggggg'

Union All

Select 'hhhhh'

Union All

Select 'iiiii'

Union All

Select 'jjjjj'

Commit

Tran

Waitfor

delay '00:02:00';

Backup

Database TestBak To Disk = 'E:\mssql\bak\TestBak.bak';

Print

'First insert at: ' + Cast(@Time1 as varchar)

Print

'Second insert at: ' + Cast(@Time3 as varchar)

Print

'Restore attempt to: ' + Cast(@Time2 as varchar)

Use

master;

Restore

Database TestBak From Disk = 'E:\mssql\bak\TestBak.bak'

With StopAt = @Time2,

Replace,

Recovery;

Use

TestBak;

Select

*

From

dbo.TestBak;

Go

Drop

Table dbo.TestBak

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]