Restore part of the data

Question

Post reply

Restore part of the data

Galina Likhovetsky

SSC-Addicted

Points: 422
More actions
February 27, 2008 at 5:34 pm

#120551

I have a following request from the user: to have an option to restore part of the database, based on the data.
A little explanation: the user deals with projects, which will have some other relevant objects. The Project data is just one row of one table, but there are some other tables/rows, assosiated with it. If one of the users accidentally deletes a project or just does something wrong with it, they want to be able to restore the yesterday version of the project and all relevant data from the backUp file
What approach to the problem would experts suggest?
My idea is to restore the back up to the different database and than move relevant data to the production database. It might have problems, relevant to the speed and extra disk space required.
Thank you to anybody answered
DB Developer
Can anybody suggest something more clever?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Rumor has it that creating a table on a different filegroup will do what you ask.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Galina Likhovetsky SSC-Addicted Points: 422 More actions · Answer 2

Galina Likhovetsky

SSC-Addicted

Points: 422

February 27, 2008 at 8:47 pm

#782965

Can you, please give more details?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Rumour also has it that database snapshots may allow what you want, without the need to restore a database at all.

Re filegroups - You can restore individual filegroups in SQL Server 2005, however to bring the filegroup online, you have to apply all the tran log backups (iirc) and so it may not be effective for restoring just part of a DB to a specific point. DO test this though, I haven't done much with filegroup backups before.

Database snapshots are a new SQL 2005 feature. they act like a read-only point-in-time view of a database. If you create a snapshot at 7am, thenmake changes to the source DB, the snapshot will still show the data as it was at 7am. You can use that to retrieve data that's been deleted or changed in the source DB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Hi

Jeff , Gail -

Does this mean creating each table in a diff file group . This way user will have option to restore only the table that user requires to be restore ? Or have i misunderstood ?

"Keep Trying"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

I believe that is correct... I say "believe" because I've never had to do it... I've only read about it. Gails snapshot option is also viable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Galina Likhovetsky SSC-Addicted Points: 422 More actions · Answer 6

Thanks, everybody.

I believe the filegroups options will not be OK for me, because I will need to restore not one or two tables, I will need to restore number of RECORDS from different tables.

But the snapshot option will work, I think.