September 6, 2010 at 3:43 am
Hi,
usually I am using the following procedure to perform Point in Time Restores in SQL Server 2005 / 2008:
- Backing up Tail Log (the current transaction log)
- Restore the latest DB backup using WITH NORECOVERY
- Restoring the corresponding transaction logs
MSDN states that a Point in Time Restore is also possible without going through the manual WITH NO
RECOVERY process:
http://msdn.microsoft.com/en-us/library/ms190982(v=SQL.90).aspx
I have tested this the following way:
- Created full backup of database at 10:30
- Changed data at 10:45
- Changed data at 10:50
- Backed up transaction log at 10:55
- Restored Database with Point in Time set to 10:47
However, after the restore the database still had the values from 10:50 in it and not the ones from
10:45.
When doing the restore the conventional way with manually restoring the transaction logs, I've got
the data from 10:45 back.
Any ideas why this might be the case?
Thanks
Michael
September 6, 2010 at 4:11 pm
All the BOL article is saying is how to perform the same operation as one would through T-SQL but doing it through SSMS instead. It is saying if you have additional files to restore, select the "Restore with No Recovery", If you have no other files to restore select "Restore with Recovery" and if you are restoring to a read-only database select "Restore with Standby".
I am not sure about the behavior you are seeing though. Can you post the messages that are displayed after the restore completes?
Joie Andrew
"Since 1982"
September 7, 2010 at 3:51 am
Joie Andrew (9/6/2010)
All the BOL article is saying is how to perform the same operation as one would through T-SQL but doing it through SSMS instead. It is saying if you have additional files to restore, select the "Restore with No Recovery", If you have no other files to restore select "Restore with Recovery" and if you are restoring to a read-only database select "Restore with Standby".
Thanks for your reply. The point is: If I would like to restore one database and one transaction log at the same time I have the ability to specify a point in time. In this case I am assuming the following: The restore process first restores the DB, puts the DB automatically in Restoring state (I can actually see this!) and then restores the transaction log to the point in time I select!
I am not sure about the behavior you are seeing though. Can you post the messages that are displayed after the restore completes?
There is just the message that the restore completed successfully. If I chose both the DB and transaction log to restore, it always restores to the latest point in time (the point where the log backup was taken). If I only select the DB, it restores to the point where the DB backup was taken. Restoring to a particular point in time however doesn't work this way (even when selected!).
Let me explain again:
- I have a database backup from 10:30 and a transaction log backup from 11:00
- I'm selecting to restore the database in SQL Management Studio
- I specify a particular point in time from when I want the data to be restored
- Management Studio automatically selectes the database backup and the log backup
- I'm starting the recovery process (using WITH RECOVERY)
During the restore process I see the following:
- Database is being restored and is put in Restoring state for a few seconds
- Transaction log backup is being restored
- Database is online and can be used again
Basically it looks like it is the manual process (first DB restore with WITH NORECOVERY, then log restore) in fully automatic! And that is what I understand from the article. However, even when specifying a particular point in time, the process always restores to the latest point in time! But it definitively restores from the transaction log!
September 7, 2010 at 4:02 am
when you do the restore after full backup, you database must in norecovery mode.
Now, start restoring your logbackup.
For pointintime recovery you need to mention the timestamp which in t-sql referred as stopat. So in EM you will see the option, "To a point in time" and then 2 option,
most recent
and
a specific date and time.
----------
Ashish
September 7, 2010 at 4:04 am
ashish.kuriyal (9/7/2010)
when you do the restore after full backup, you database must in norecovery mode.Now, start restoring your logbackup.
For pointintime recovery you need to mention the timestamp which in t-sql referred as stopat. So in EM you will see the option, "To a point in time" and then 2 option,
most recent
and
a specific date and time.
also with your last restore, you need to select "restore with recovery" to put the database in operational mode.
----------
Ashish
September 7, 2010 at 4:07 am
ashish,
thanks, however, I guess you haven't properly read my question. I actually do know how to perform a manual point in time restore! please see the added information in my last post.
September 7, 2010 at 4:11 am
ok, let me try. Will come back to you soon
----------
Ashish
September 7, 2010 at 4:38 am
here is my finding...
I did the full backup.
then modified a table value
take log backup
then modified the value back to original
take log backup.
Restored on existing database and it showed me all the backupset.
I have select the full and first logbackup only and it did restore only uptill that perticular point and I am able to see the record which i modified in my first statement.
Am I missing any point still?
----------
Ashish
September 7, 2010 at 4:47 am
Thanks for your help. If I understand correctly, you have not specified a point in time but just selected the first logbackup and chose "Most recent possible"? This is working here as well. Could you test the following:
- Create full backup (i.e. 10:00, table value == 0)
- Modify table value (i.e. 10:05, table value == 10)
- Modify table value (i.e. 10:10, table value == 20)
- Create log backup (i.e. 10:15, table value == 20)
Then choose "restore database". You've got two backups: The DB backup from 10:00 and the log backup from 10:15. Now specify a particular time to restore - i.e. 10:07. This should restore the table value to 10 again.
This is what doesn't work in my case. It will always restore to the most recent point in time, i.e. table value 20.
September 7, 2010 at 4:53 am
Are you saying it works when you execute a RESTORE statement, but not if you use the GUI? Have you tried running a Profiler trace so that you can see exactly what code is run when you use the GUI?
John
September 7, 2010 at 4:58 am
No, it is working when I perform the manual process described in my first post:
- Log Backup
- Restore Database with WITH NORECOVERY (using restore Database)
- Restore Log (using restore Transaction Log)
But it does not work to a point in time when specifing a point in time in "restore Database". Even though I understand from that MSDN article this should be possible (and the possibility to specify a point in time indicates this as well).
Im only using the GUI btw. There is no real problem as the manual restore works fine, however, I just would like to know why the other process doesn't.
September 7, 2010 at 5:11 am
i think i agree with you, when i give the option to stopat and tried to see what the script code is, its no whevere mentioning the restore with stopat.
Need to dig it further.
----------
Ashish
September 7, 2010 at 5:36 am
Thank you. So might this actually be a bug? The version I am using btw is 2008.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply