January 14, 2011 at 4:01 am
mmm.. someone said to me that we can restore our database without backing up that database first...
This is scenario he told to me :
First : create new table.. for instance : table 'Employee' with two columns which are : 'ID' and 'Name'
Then.. insert 2 new data into that table :
A001 | Billy
A002 | John
After that, we backup the database containing that table in full-backup manner (with Recovery-Model set to 'SIMPLE') ... Then we will get our backup file .. right ?
After that... make some changes in that table such as insert 2 new data into 'Employee' table :
A001 | Billy
A002 | John
A003 | George --> This is our new data
A004 | Dylan --> This is our new data too
And don't make any backups after that !!!
After that, let us restore our database with previously created backup file.. And then there are the results of the 'Employee' table after restore action :
A001 | Billy
A002 | John
And then he said that we can get our 'George' and 'Dylan' data back again.
One question in my mind now is how we get that 'George' and 'Dylan' data back by using restore technique (please remember : we didn't backup database when we had 'George' and 'Dylan' data) in sql server or another technique besides manually inserting those 2 data again 🙂 ???
Is it possible to get those two back in 'Employee' table ???
Please give your reasonable answer.. And i'd like to appreciate all of your answers....
January 14, 2011 at 6:10 am
anonymous.surfer07 (1/14/2011)
mmm.. someone said to me that we can restore our database without backing up that database first...This is scenario he told to me :
First : create new table.. for instance : table 'Employee' with two columns which are : 'ID' and 'Name'
Then.. insert 2 new data into that table :
A001 | Billy
A002 | John
After that, we backup the database containing that table in full-backup manner (with Recovery-Model set to 'SIMPLE') ... Then we will get our backup file .. right ?
After that... make some changes in that table such as insert 2 new data into 'Employee' table :
A001 | Billy
A002 | John
A003 | George --> This is our new data
A004 | Dylan --> This is our new data too
And don't make any backups after that !!!
After that, let us restore our database with previously created backup file.. And then there are the results of the 'Employee' table after restore action :
A001 | Billy
A002 | John
And then he said that we can get our 'George' and 'Dylan' data back again.
One question in my mind now is how we get that 'George' and 'Dylan' data back by using restore technique (please remember : we didn't backup database when we had 'George' and 'Dylan' data) in sql server or another technique besides manually inserting those 2 data again 🙂 ???
Is it possible to get those two back in 'Employee' table ???
Please give your reasonable answer.. And i'd like to appreciate all of your answers....
It would seem that someone is trying to fool you.
In a Simple Recovery Model, all of the data is committed to the Data File as soon as the checkpoint is reached at which time the transaction no longer exists anywhere but the query to insert the data and the datafile.
In the scenario above, you would only be able to recover George and Dylan if another Full-backup were made or the means to insert the data can be reproduced (i.e. scripting was saved, data was imported from another resource via interface, application stores transactions for redundancy to another file).
If you want to minimize data loss you need to plan very carefully. That also means you need to look at the methods used to restore the data not just backing it up. Start by asking how much data can we afford to lose and build the ideal scenario from there.
Things like cost, time, and space (disk) are going to prevent the idyllic business continuity plan, but tweaks can be made to minimize loss for relative low cost.
Regards, Irish
January 14, 2011 at 6:50 am
I recommend you have a good read of this article by Paul Randal, to get a better idea of what happens under the hood during restores
January 14, 2011 at 7:29 am
Nope. Not with the log in simple recovery. Now, if the log were in full recovery you could either backup the log & retrieve it from there, or use one of the log explorer tools and retrieve the data that way.
I'm not sure what they were getting at.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply