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,
Answer by Fatherjack ·
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.