restore point
-
Is there something equivalent in SQL server like create guaranteed restore point in oracle?
I know in SQL server if we setup backup chain with transaction logs, we can restore to any point of time.
But just wonder if anything like creating guaranteed restore point in oracle?
Thanks
- sqlfriends - Tuesday, July 25, 2017 12:53 PM
Something like database snapshots is probably the closest thing.
Database SnapshotsSue
- sqlfriends - Tuesday, July 25, 2017 12:53 PM
sounds equivalent to backup marks, read more on them at this link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
- Perry Whittle - Wednesday, July 26, 2017 6:25 AM
Could be but it's usually used with flashback database which you use with guaranteed restore point. Those features are designed to be an alternative to point in time recovery as flashback database doesn't require restoring the last backup to go back to the guaranteed restore point. But it is like marking the log in that it's just an alias to the system change number you would use to recover to that SCN, you can have multiple guaranteed restore points.
It's an alternative to restoring the backup to get back to a point in time but it's also meant as an alternative to snapshots as well. So there really isn't anything the same in SQL Server.
Sue
-
Thank you all.
The purpose for that used in Oracle in our case is to make a guaranteed point of recovery, then testers will do some test data entry, then later after test, we will restore to that guaranteed point of recovery to be clean.The snapshot database in SQL server looks very different, it first is read only, and it is also updated when source database is updated.
The transaction log mark up in SQL server looks similar, but it is more transaction based and usually related with multiple databases.
So probably in SQL server the most similar way is restore database at point of time as I understand.
Thanks,
- sqlfriends - Wednesday, July 26, 2017 10:50 AM
It can be but it can also be used for a single database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Point in time is similar but not the same as you are doing a series of restores. What you do depends on why you were using guaranteed point of recovery in the first place. You would need to weigh out what works best for your environment, situation to meet those needs. Some examples could be -
If the reason you were using guaranteed point in time recovery is to not take a full backup at that time then you could use point in time recovery.
If the reason is the time it takes to restore a full backup when testing, you can revert the database to a snapshot taken immediately before the testing. It would be faster than a restoring the full backup and is listed as one of the uses of snapshots.
If the reason is you just want to get the database back in the state is was before testing and have updates/growth patterns where snapshots wouldn't work, you could just take a full backup before testing, do testing and then restore that full backup after testing.
I've usually done the full backup and restores for testing but sometimes that can be prohibitive due to size.Sue
-
Thanks, I am not quite clear when you say snapshots.
Snapshots are readonly, and it will be updated as source database is updated.
How can the database be reverted to the snapshot?Thanks
-
I think you're misunderstanding how SQL handles a database snapshot. When you take a snapshot, it gets a new name (so a snapshot might be called "database_date") and yes, is read-only. But, the snapshot does NOT change regardless of what data changes happen to the "source" database.
When you're done with a snapshot, you have two options for what to do with it:
A) Delete the snapshot, leaving any data changes made to the source database alone
B) Revert the source database back to the state it was in when you took the snapshotI've used snapshot for both of the purposes above, the first instance was a customer who required an absolutely static, unchanging data set for some reports that needed to be generated (case A,) which allowed them to continue to use the application as normal as well (the users generating the reports pointed to the snapshot.)
Case B I set up for a developer who requires his data to be exactly the same for performance testing, across a multitude of tables. So when he gets the data set up to his start condition, he takes a snapshot, tests (which changes the source DB,) then reverts back to the snapshot so he can retest from the same start point.Not sure what documents you've been looking at for snapshots of SQL databases, take a look at this one from MS:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server
(OK, this is the same link Sue posted further up) -
Thank you.
I see the 4 line in the llinked article:
As the source database is updated, the database snapshot is updated. - sqlfriends - Wednesday, July 26, 2017 12:06 PM
Yes, the snapshot is updated with the unchanged / original data page. From the article:
Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. The same process is repeated for every page that is being modified for the first time. To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.
-
Thank you. I think I need to read and understand snapshot more , and when to use it.
You explanation is greatly appreciated. -
You're quite welcome
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply