restore to point of time

Question

restore to point of time

sqlfriend

SSC Guru

Points: 52781
More actions
September 24, 2012 at 10:47 am

#262048

Recently I tried to restore a database to a point of time using SSMS, for this case I would want to restore to Last Friday status.
I select the database, and chose the point of time, then I script that out.
I see it points to a database backup file we backed up every day but the time stamp is already changed to this monday early morning.
But since the backup is done every day and overwritten, so I don't think it is right. I suppose it will give an error of something, but actually it will restore to Monday morning status.
So is this a bug when using Restore to point of time from SSMS UI?
Thanks,

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

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

sqlfriends (9/24/2012)
Recently I tried to restore a database to a point of time using SSMS, for this case I would want to restore to Last Friday status.
I select the database, and chose the point of time, then I script that out.
I see it points to a database backup file we backed up every day but the time stamp is already changed to this monday early morning.
But since the backup is done every day and overwritten, so I don't think it is right. I suppose it will give an error of something, but actually it will restore to Monday morning status.
So is this a bug when using Restore to point of time from SSMS UI?
Thanks,

The flaw is in the backup strategy if you want to be able to do that type of restore.

You cannot restore to a point in time if you do not have a backup from before that point in time, and all the transaction log backups from the time of the full backup until the point in time you want to restore to.

SQLmountain SSCommitted Points: 1505 More actions · Answer 2

I am a bit confused since you indicate your backups are being overwritten. As in - new backup overwrites old.

If you do know that you have a backup that is just not showing up in SSMS you may just have to browse for it. In the SSMS "Restore Database" screen, hit the "From device" radio button and browse for the location of your file.

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

bret.christoe (9/24/2012)
I am a bit confused since you indicate your backups are being overwritten. As in - new backup overwrites old.
If you do know that you have a backup that is just not showing up in SSMS you may just have to browse for it. In the SSMS "Restore Database" screen, hit the "From device" radio button and browse for the location of your file.

We do have maintenance plan setup to do full backup at weekend, then daily differential, and hourly transaction. But I just noticed a developer SSIS package bakup in ETL backup the database daily and restore it to another test server, the backup is overwritten for this case. The result is it breaks my maintenance plan backup chain although the files names are different.

And when I tried to restore a backup to a point time, it try to use his version.

I changed his backup to copy_only.

But for this post, I just wonder it seems the point of time restore function is not smart enough to find out the time stamp of a backup file, it can only get the name of the backup file correctly.

Thanks,