question

user-1756 avatar image
user-1756 asked

Restore Filegroup for Patitioned Table

Here is the scenario:

SQL Server 2005 Enterprise.

I have a single table partitioned into many file groups using a single partition scheme and a single partition function. There is a 1-1 relationship between each filegroup and file. We perform nightly backups of the primary and all of the filegroups for the database each with their own individual backup files.

Let's say a user accidently deletes 100 rows from the table which just happens to be in the first partition file/filegroup. Is it possible to restore just the single file/filegroup to the same state it was at from the last nightly backup?

The problem I have having is that if I perform the restore of just the single filegroup it is still off-line. They only way I have been able to bring it on-line is to get the tail end of the transaction log and then restore this "with recovery" after the file group restored. If I do this then the deletes are re-applied. This is not what I am looking for. I am just looking to bring those deleted records back for the single table and ignore any other transactions.

Maybe this is not possible. I could of course restore the primary and all of the file groups from last nights backup but this is an extremely large database.

Thank You in advance,

Mark

restorepartitioning
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered

If I do this then the deletes are re-applied

Only if you do not use the "STOPAT" clause. You should stop the restore just before the delete took place.

see BOL

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLMaster avatar image
SQLMaster answered

Is is not possible to restore the database backup on different server and then get the deleted rows from that newly restored database.

Why you are looking to jeopardise the production database?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

In cases like this is is possible to do what SQLMaster has mentioned or you could actually get just the required data out of the backup using a tool like SQL Data Compare from Red Gate. This sort of tool can be a real benefit in situations like this as it simply compares the table in the backup with the live table and lets you synchronise the data between the two. http://www.red-gate.com/products/SQL_Data_Compare/index.htm.

Either method will work, the third party tool has the benefit of not needing to restore the database (and therefore the disk space for it) as a copy and its much faster.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.