Testing point in time recovery

Question

Testing point in time recovery

Trooper09

SSCrazy

Points: 2047
More actions
March 19, 2009 at 6:41 pm

#132422

Environment : SQl server 2k5 (80 Compatability mode)
I planning to come up with a new point in time recovery solution for on e of the production databases and I am not sure how I can test it as
1 ) We do not have a hoigh availabilty solution where in I can try the new Full,diff, transactional log backups jobs on the stand by server and when the poin in time of recovery is decide , restored teh backups and compare it with the actual db.
2) I cannot run the same jobs on the prodcution and compare the backups taken as they would mess up the already built in backup jobs.
I have an option of creating a new db on a dev server and refresh it with the prod and create the new backups jobs and when restored compare the data - However, I will be able to refresh the db on a daily basis (once a day) so I will not be able to find out the work done by diff and transactional log backup.
I am not sure whether I can use transactinal replication and use the subscriber to test the backups jobs
I need to show my supervisor that the backups jobs are performing the necessary backups and will help us restore the backups and be able to recover the db when needed.
Help is appreciated. Let me know if I am missing anything.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Glenn Dorling SSCertifiable Points: 7878 More actions · Answer 1

We had a similar-sounding situation a few years ago, where we couldn't use the DR system and didn't know the data at any given time but still had to prove the recoverability.

What we did was to create a dummy table in the production system, eg:

create table ThisIsADummyTable(Id int)

go

print getdate()

We noted the time reported by getdate(), which importantly is the time on the server, then dropped the dummy table.

We copied the full backup (didn't have any interim differential backups) and log backups, including the next log backup to run after the dummy table was created. We then restored the full backup to a database on a dev server, followed by the intermediate log backups and the final log backup to the time reported by getdate() in the script above. If the dummy table was there then we knew that our backup strategy was working properly.

Trooper09 SSCrazy Points: 2047 More actions · Answer 2

Thanks Glenn,

But rite now there are baclups jobs available on the server and I need to replace the old backups jobs with the new ones.....

I cannot go with the same approach I beleive.Is there anything else that can be done....

Trooper09 SSCrazy Points: 2047 More actions · Answer 3

Trooper09

SSCrazy

Points: 2047

March 24, 2009 at 10:09 am

#965361

Any other suggestion??

Thanks.

kiran_tamboli Valued Member Points: 59 More actions · Answer 4

Glenn Dorling (3/19/2009)
We had a similar-sounding situation a few years ago, where we couldn't use the DR system and didn't know the data at any given time but still had to prove the recoverability.
What we did was to create a dummy table in the production system, eg:
create table ThisIsADummyTable(Id int)
go
print getdate()
We noted the time reported by getdate(), which importantly is the time on the server, then dropped the dummy table.
We copied the full backup (didn't have any interim differential backups) and log backups, including the next log backup to run after the dummy table was created. We then restored the full backup to a database on a dev server, followed by the intermediate log backups and the final log backup to the time reported by getdate() in the script above. If the dummy table was there then we knew that our backup strategy was working properly.

hi pranavr