Search results matching tags 'Backup' and 'Transaction log'

Point-in-time restore of database backup?

TiborKaraszi — Tue, 28 Dec 2010 16:42:00 GMT

SQL Server 2005 added the STOPAT option for the RESTORE DATABASE command. This sounds great - we can stop at some point in time during the database backup process was running! Or? No, we can't. Here follows some tech stuff why not, and then what the option is really meant for:

A database backup includes all used extents and also all log records that were produced while the backup process was running (possibly older as well, to handle open transactions). When you restore such a backup, SQL Server will from backup media copy the data pages to mdf and ndf file(s), log records to ldf file(s) and then do REDO (roll forward the log records) and possibly also UNDO (rollback open transactions - this is skipped if you specify NORECOVERY option). Why does it need the log records? Because you can do modifications in the database while the database backup is running. After backup process was started (and before it finishes) you can both modify pages not yet copied to backup media, but also pages already copied to backup media. So, the data pages in themselves do not present a consistent state of the database. At restore time, SQL Server uses the log records included in the database backup to "sync" the modifications that were performed while the backup process were running. So, what you end up with is what the database looked like at the end time of the backup process. I'm sure this is well documented somewhere in Books Online so I won't dive further into the tech stuff here.

So, what is the purpose of the STOPAT option for RESTORE DATABASE?

It is to give you an early warning, in case you try to restore a database backup which is too late for a subsequent STOPAT operation for a log restore. Here's an example:

13:00 db backup
13:10 log backup
13:20 log backup
13:30 db backup
13:40 log backup
13:50 log backup

Say you now want to restore to 13:15. Intuitively, you say that you will restore 1, 2 and for 3 you do STOPAT 13:15. Yes, that is right.

But say you are under stress, and perhaps even used some tool which confuses things for you? So, you end up restore 4, 5 and for 6 you try STOPAT 13:15. Clearly, you can't stopat 13:15 for backup number 6 (it only covers 13:40 to 13:50). I.e., restore of 6 gives you an error message. So, you have to re-start the restore process (1, 2 and 3), which could take a long time (if the backups are large). If you had specified STOPAT 13:15 when you restore backup 4, SQL Server would have given you an error immediately, so you wouldn't have wasted valuable time restoring unnecessary backups!

That is the purpose of STOPAT for RESTORE DATABASE.

Why we never want to trick SQL Server into rebuilding a log file

TiborKaraszi — Thu, 14 Oct 2010 17:04:00 GMT

"Never" is a strong word, so let me say that we really really want to avoid it, if at all humanly possible. In short, we will have a (potentially) broken database, both at the physical level and at the logical level. Read on.

Just to be clear, when I refer to a "log file" here, I'm talking about a transaction log file, an .ldf file. Ever so often we see in forums how log files are "lost", "disappears" or so. Now, SQL Server relies on the ldf file at startup to bring the database to a consistent state. This is known as "recovery", or "startup recovery". This is pretty well known, I for instance teach this at every intro level admin course. So, what if

The ldf file isn't there?
Or isn't accessible to the db engine?
Or is broken somehow?
Or is from a different point in time from the data file? (You'd be surprised to all the things one see over the years.)

Well, SQL Server will do the only reasonable, refuse us into the database and produce an error message (in eventlog etc).

What we see from time to time, is trying to "trick" SQL Server into re-creating an ldf file. So, why is this so bad? I will try to explain why. Let me first say that SQL Server doesn't do these things to be mean to us, or to prove a point. If SQL Server know that the ldf file is not necessary for recovery (the database was "cleanly shutdown"), then it can and will re-create a log file for us at startup. The problem is that it isn't these cases we see in forum. The cases we see in the forums is when this didn't happen. SQL Server relied on the ldf file in order to bring the database to a consistent state.

Enough beating around the bush, here is an example of why we don't want to trick SQL Server to forcefully re-create a log file:

Say you have a transaction in which you add a row to the order header table (oh) and five rows to the order details table (od). Physically, each command is reflected in the tlog, the page is modified (but still cached) and at commit, the log records are written to the ldf file. There are lots written about these algorithms, but the concepts are pretty simple. For more details, read this.

Say that we forcefully stop SQL Server, delete the ldf file, start SQL Server, see that the database isn't accessible and somehow "trick" SQL Server into creating an ldf file for us. What is the problem? Why is this so bad? The problem is that you can have no confidence in the state of your data, both at the physical level and at the logical level. Let me start explaining what I mean by the logical level and problems at this level:

The logical level
By the logical level, I consider the user data. The rows in the tables, quite simply. We inserted one row in the oh table and five rows in the od table. These can be inserted into a total of two pages or 6 pages (in reality more, since each table is likely to have indexes etc, I'll touch on this when we get to the logical level). Say that three of order details rows have been written to disk, but not the order header row, and not the other two order details rows. This is just an example; you can pick and choose any combo you want. It can get just as bad as you can imagine! You might think that the pages are in the end written in the same sequence as we modified them. No, that is now how the cookie crumbles (read the article I posted link to). You can probably imagine other scenarios, closer to your data. For instance, we will not do some certain modification to a table unless some other prior modification was also performed. In essence, rebuilding a log file leave us with no confidence in the data. Are you prepared to go through your database and manually verify all the data in there? Majority of you will say "no", and it might not even be doable (data volumes, lack of information to verify etc). So, logical inconsistencies are bad. Really bad. We don't want this. Ok?

The physical level
This log is not only used for "user-data". It i also used for system data. Tables has indexes, where each row in a table is reflected by a row in each index. We have allocation pages stating what storage is used. We have IAM pages and PFS pages. We have linked lists. And whatnot. You probably realize that these structures also require that some modification is performed in full or not at all. (an extent allocation will be reflected in both the GAM or SGAM page and also in th extent data itself, etc). What do you think is used to protect this? Yes, you got it, the ldf file. Trick SQL Server into re-creating an ldf file and you have all sorts of physical inconsistencies. Actually, physical inconsistencies are a bit better than logical since we do have a way to check for these. I'm of course talking about the mighty DBCC CHECKDB command, a command with lots of smarts (right Paul?) to check that the db is consistent at the physical level. And what if it isn't? CHECKDB spits out errors. Sure it has repair options, but those generally mean (unless you are the type of person who wins are lotteries) that the repair will rip out whatever makes the db inconsistent resulting in data loss (which also has no knowledge of what you might consider logical consistent data).

So, what to do?
I hope the answer is obvious. Don't get into this situation in the first place. Don't go deleting ldf files for instance. Do take backups frequently enough so you don't end up in a situation like "I need to rebuild the log or I'm toast.". If something strange happens, don't start mucking about with the system unless you know what you are doing. Hire a specialist, call MS support or so. If I had a brand new Ferrari, I wouldn't disassemble the engine in case I hear weird noise from the it. Heck, I barely pop the hood of my Beamer!

And no, I won't get into what commands can be used to re-build the ldf file. I expect all readers of this to not get into a situation where it is needed. :-)

(I understand one can encounter a machine with no backups and something happens to the ldf file, and such scnearios. Of course I do. I feel really really bad every time I read about such a situation, because there is no graceful way to handle it. That is why I have such a harsh tone above. I don't want this to happen to anybody. One would hope that this is obvious, but nothing ever is on the Net. So I've learned. So, please leave such unconstructive comments out of the discussions!)

Restore database to the point of disaster

TiborKaraszi — Sat, 27 Mar 2010 09:50:00 GMT

This is really basic, but so often overlooked and misunderstood. Basically, we have a database, and something goes south. Can we restore all the way up to that point? I.e., even if the last backup (db or log) is earlier than the disaster?
Yes, of course we can (unless for more extreme cases, read on), but many don't realize/do that, for some strange reason.

This blog post was inspired from a thread in the MSDN forums, which exposed just this misunderstanding. Basically the scenario was that they do db backup and only log backup once a day. Now, doing log backup that infrequent is of course a bit weird, but that is beside the point. The point is that you can recover all the way up to the point of disaster. Of course, it depends on what the disaster is (don't expect too much if the planet blows up, for instance).

Since "log backup only once a day" was mentioned, I will first elaborate a bit on frequency for database vs log backups. For the sake of discussion, say we do both db and log backup once a day. You say:
"What? Both db backup and log backup once a day - why would anybody do that way? Wouldn't one do log backup more frequently than db backup?"
Yes, of course (but I actually see such weird implementations from time to time). But again, that doesn't change the topic at hand, but I will first elaborate on this; just so we don't see blurring comments later arguing this irrelevant argument.

So, lets first sort out two different cases:

A) Log backup before the db backup
1: db backup
...
2: log backup
3: db backup
crash
Here we will use backup 3 when we later will restore.

B) Db backup before log backup
1: db backup
...
2: db backup
3: log backup
crash
Here we will use backup 2 and 3 when we later will restore.

You see that A) and B) are really the same thing? What is relevant is that we have all log records available (in ldf file/log backups) since the db backup we chose to use as starting point for the restore. Actually, for A), we could might as well use backup 1 and 2 (and skip 3)!

"Hang on", you say, "we're not done yet. What about the modifications since the last log backup! Gotcha!"
No worries, this is where it gets interesting, and below is really the heart of the topic. Clearly, we need to get the log records out of the ldf file into a log backup (file). If we can do that, then we will call this backup number 4, and use as the last backup for our restore. After doing that restore, we have no data loss!

So, how do we produce a log backup after a disaster?
It depends on the disaster! Let's discuss a few scenarios:

a) Planet Earth blows up.
No can do. I doubt that anyone of you has mirrored data centers on Moon or March; and also people stationed off-Earth for these situations. Of course, I'm being silly. But my point is that you can always have a disaster such that you can't produce that last log backup. No matter how much you mirror: if the disaster takes out all mirrors, then you are toast. Remember that when you talk SLA's. That fact is not popular, but it can't be argued. It is all about limiting the risk exposure - not eliminating it. Anybody who believes we can eliminate risk exposure is dreaming. Agreed? Good. Let's move on to (hopefully) more realistic scenarios:

b) Something happens with the data file(s), lost, corrupt or so. Ldf file is still there.
This is the easy case, but so often overlooked. What you do now is to backup the log of the damaged database, using the NO_TRUNCATE option. Something like:
BACKUP LOG dbname TO DISK = 'C:\dbname.trn' WITH NO_TRUNCATE
Yes, it really is that simple. Then restore backups from above, including this last log backup. Don't believe me? Test it.

Create database and table
Insert some data
Do db backup (1)
Insert some more data
Do log backup (2)
Insert some more data
Stop SQL Server
Delete mdf file
Start SQL Server
Do log backup using NO_TRUNCATE (3)
Restore 1, 2 and 3.

c) Something happens with the database. Ldf file is NOT still there.
Clearly, if the ldf file is really gone, we can't do a log backup - how much as we might want to. Remember the old days, when redundancy for disks (RAID) wasn't as common as today? "If there's anywhere you want redundancy, it is for the transaction log files!"

d) Something happens with the data file(s), lost, corrupt or so. Ldf file is still there. The installation is toast - we can't start SQL Server.
This seems a bit more nerve-wracking, right? Not to worry, just do the right steps and you will be fine. You probably ask now:

"But how can we backup the transaction log when our SQL Server won't start?"

That is a good question. You need to get that ldf file to a healthy SQL Server, and make SQL Server believe this is the ldf file for a broken database on that instance. It is not really complicated. Just use a dummy database on that SQL Server as intermediate - to get the right meta-data into that SQL Server, so in turn it will allow you to produce this last log backup. I will show just that:

I have two instances on my machine (named "a" and "b"). I will create and damage a database on instance a, and then produce a log backup for that orphaned ldf file a different instance, b. I will pretend these are on two different machines, using separate folders for the database files "C:\a" and "C:\b". Here's the T-SQL, starting with instance a:

IF DB_ID('x') IS NOT NULL DROP DATABASE x 
GO 
CREATE DATABASE x 
ON PRIMARY  
(NAME = N'x', FILENAME = N'C:\a\x.mdf', SIZE = 10MB, FILEGROWTH = 10MB) 
LOG ON  
(NAME = N'x_log', FILENAME = N'C:\a\x_log.ldf', SIZE = 5MB, FILEGROWTH = 5MB) 
GO 
ALTER DATABASE x SET RECOVERY FULL 
CREATE TABLE x.dbo.t(c1 INT IDENTITY) 
INSERT INTO x.dbo.t DEFAULT VALUES --1 
BACKUP DATABASE x TO DISK = 'C:\x.bak' WITH INIT 
INSERT INTO x.dbo.t DEFAULT VALUES --2 
BACKUP LOG x TO DISK = 'C:\x1.trn' WITH INIT 
INSERT INTO x.dbo.t DEFAULT VALUES --3 
SELECT * FROM x.dbo.t 

--Stop SQL Server and delete below file 
--C:\a\x.mdf 
--Start SQL Server 

--Oops, damaged database...: 
SELECT * FROM x.dbo.t 

--Stop SQL Server, pretend installation is toast

Do we agree that we have a damaged database, and there has been done modifications since the last log backup? Ok, fine. We now pretend that SQL Server instance "a" doesn't start anymore. So, I will try to produce a log backup from that ldf file on instance "b":

IF DB_ID('x2') IS NOT NULL DROP DATABASE x2 
IF DB_ID('x') IS NOT NULL DROP DATABASE x 
GO 
CREATE DATABASE x2 
ON PRIMARY  
(NAME = N'x2', FILENAME = N'C:\b\x2.mdf', SIZE = 9MB, FILEGROWTH = 8MB) 
LOG ON  
(NAME = N'x2_log', FILENAME = N'C:\b\x2_log.ldf', SIZE = 6MB, FILEGROWTH = 7MB) 
GO 

--Stop SQL Server and delete below files 
--C:\b\x2.mdf 
--C:\b\x2_log.ldf 

--Copy the C:\a\x_log.ldf to C:\b\x2_log.ldf 

--Start SQL Server 

--Produce our last log backup: 
BACKUP LOG x2 TO DISK = 'C:\x2.trn' WITH INIT, NO_TRUNCATE 

--Restore the database, up to last transaction. 
--Investigate logical file names for MOVE options first: 
RESTORE FILELISTONLY FROM DISK = 'C:\x.bak'  

RESTORE DATABASE x FROM DISK = 'C:\x.bak'  
WITH 
 NORECOVERY 
,MOVE 'x' TO 'C:\b\x.mdf' 
,MOVE 'x_log' TO 'C:\b\x_log.ldf' 

RESTORE LOG x FROM DISK = 'C:\x1.trn' WITH NORECOVERY 
RESTORE LOG x FROM DISK = 'C:\x2.trn' WITH RECOVERY 

--Data there? 
SELECT * FROM x.dbo.t 
--See? That wasn't so difficult.

Note how I even named the dummy database differently on instance b, with different physical file names and different file sizes (all compared to what we had on instance a). Typically, you will use same database name and same filename, but I want to show that we don't really have to know a whole lot about the damaged database in order to produce a log backup from the ldf file!

Case closed.

Are log records removed from ldf file for rollbacks?

TiborKaraszi — Mon, 22 Mar 2010 09:34:00 GMT

Seems like a simple enough question, right? This question (but more targeted, read on) was raised in an MCT forum. While the discussion was on-going and and I tried to come up with answers, I realized that this question are really several questions. First, what is a rollback? I can see three different types of rollbacks (there might be more, of course):

Regular rollback, as in ROLLBACK TRAN (or lost/terminated connection)
Rollback done by restore recovery. I.e., end-time of backup included some transaciton which wasn't committed and you restore using RECOVERY, so SQL Server need to rollback this transaction (UNDO).
Rollback done by crash (restart) recovery. I.e. SQL Server was shut down with some open transaction.

I'm going to try to show whether log records are removed or still present for these three types of rollback situations. I will use the fn_dblog function. This isn't documented or supported, but search the internet and you will find how to use it. The result isn't documented either, of course, so we have to guess a bit what the various values mean...

The TSQL script has some common parts (the same part executed repeatedly, once for each test):

Create a database
Make sure it is in full recovery
Do a database backup
Create a table (myTable)
Insert a row into myTable. This last operation generates 5 log records for the myTable table: one for the PFS page, two for IAM pages, one format page for the heap and the last one is a LOP_INSERT_ROWS.
Start a transaction
Insert one more row into myTable. We now have one more log record for myTable (LOP_INSERT_ROWS). Looking at the transaction id for this last insert, we see two log records (one LOP_BEGIN_XACT and the LOP_INSERT_ROWS). Note that this transaction is now open!

Here is the above mentioned first part of the script:

USE master 
IF DB_ID('x') IS NOT NULL DROP DATABASE x 
GO 
CREATE DATABASE x 
GO 
ALTER DATABASE x SET RECOVERY FULL 
BACKUP DATABASE x TO DISK = 'C:\x.bak' WITH INIT 

USE x 
CREATE TABLE myTable(c1 INT IDENTITY) 
INSERT INTO myTable DEFAULT VALUES 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--5 rows 

BEGIN TRAN 
INSERT INTO myTable DEFAULT VALUES 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--6 rows 

SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID]  
                          FROM fn_dblog(NULL, NULL) 
                          WHERE AllocUnitName LIKE '%myTable%' 
                          ORDER BY [Current LSN] DESC) 
--2 rows

Now, on to the different cases:

1. Regular rollback, as in ROLLBACK TRAN.
It seems reasonable to me that SQL Server will just add some "rollback transaction" log record here. So, let's try that (continuing on above first part)... We now have 7 log records for myTable, with an added LOP_DELETE_ROWS which undo the previously insert. And for our transaction ID, we have 4 rows, with added two rows being LOP_DELETE_ROWS (compensation log record) and a LOP_ABORT_XACT.

--Regular ROLLBACK 
ROLLBACK TRAN 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--7 rows 

SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID]  
                          FROM fn_dblog(NULL, NULL) 
                          WHERE AllocUnitName LIKE '%myTable%' 
                          ORDER BY [Current LSN] DESC) 
--4 rows

2. Restore recovery
Now, what does this really mean? Restoring a transaction log backup is a good example. SQL Server read log records from the transaction log backup file and writes them into the LDF file. This is what we call the "data copy" phase. Then SQL Server performs REDO (a.k.a. roll forward). And finally, SQL Server performs UNDO (roll back), assuming we don't do the restore using the NORECOVERY option. Restoring from a database backup isn't any differene except the log records are of course read from the database backup file.

Here it seems likely that SQL Server will wipe more or less anything from the LDF file as soon as the database is restored and brought on-line. Why? The log in this newly restored database can't serve as a starting point for a restore operation for this database. You first need a database backup. So, no use hanging onto log records either! Let's see if we can verify that:

--Restore recovery 

--Do this from different connection 
BACKUP LOG x TO DISK = 'C:\x.bak' 
--We now have open transaction in database! 

--Perform RESTORE 
ROLLBACK 
USE master 
RESTORE DATABASE x FROM DISK = 'C:\x.bak' WITH REPLACE, NORECOVERY 
RESTORE LOG x FROM DISK = 'C:\x.bak' WITH FILE = 2, RECOVERY 

--Rollback was done, and database berought online.  
--What log records do we have? 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--0 rows 

--Use the transaction ID from earlier SELECT 
SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = '0000:00000203' 
--0 rows

So, SQL Server will remove user-defined stuff from LDF file after restore recovery was performed. Makes sense.

3. Crash recovery (a.k.a. restart or startup recovery)
I couldn't really guess here. So, lets give it a spin immediately and see:

--Crash recovery 

--Do below from separate connection 
SHUTDOWN WITH NOWAIT 

-- startup SQL Server and examine the log records: 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--5 rows 

--Use the transaction ID from earlier SELECT 
SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = '0000:00000204' 
--0 rows

Hmm, so SQL Server removes the log record after the rollback was performed. Makes sense.

But this got me thinking some more. How can this be done... physically? SQL Server would just "reverse" the head of the log a bit. But what if we have later transactions for other connections, which has been committed? SQL Server can't ignore those, of course. These need to be kept in the LDF file for subsequent log backups. OTOH, I doubt that SQL Server will somehow physically delete things "in the middle" of an ldf file. Time for yet another test:

--Crash recovery, part two 

--Do below from separate connection 
--First something which creates more recent log records 
CREATE TABLE y(c1 INT) INSERT INTO y(c1) VALUES(1) 
SHUTDOWN WITH NOWAIT 

-- startup SQL Server and examine the log records: 

SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%myTable%' 
--7 rows 

--Use the transaction ID from earlier SELECT 
SELECT * FROM fn_dblog(NULL, NULL)  
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID]  
                          FROM fn_dblog(NULL, NULL) 
                          WHERE AllocUnitName LIKE '%myTable%' 
                          ORDER BY [Current LSN] DESC) 
--4 rows

Now the log records for our rolled back transaction are still there! So, just as when we did a regular rollback, SQL Server inserted a LOP_DELETE_ROWS to reflect the undo of the INSERT, and then a LOP_ABORT_XACT.

Conclusion
Isn't it beautiful when it all makes sense? Here are my conclusions, whether log records are kept or removed from transaction log file (LDF) for various types of rollback scenarios:

Regular rollback. Log records are not removed. Compensation log records are logged, reflecting undo of the modifications, and then an LOP_ABORT_XACT is logged.
Restore recovery. Log records are removed.
Crash recovery. It depdends. If you have a transaction which is at the very head of the log, then those log records can be removed. If there are other, subsequent committed transactions, then compensation log records are logged, reflecting undo of the modifications, and then a LOP_ABORT_XACT is logged.

Do you perform log backup for the model database?

TiborKaraszi — Wed, 18 Nov 2009 12:05:00 GMT

Are you stupid, you might think... But stop and think for a while. Model is no different from other databases. And by default it is in full recovery model. So as soon as you do your first database backup (you do backup your system databases, right?) the log for model will start filling up and autogrow. "But, hey, I don't do any modifications in model!", you probably say now. Fair, but other things happens in each database from time to time. Bottom line is that ldf file for model will start growing after a while . Perhaps not huge, but I find it "un-neat" to have a model with 3 MB mdf file and 20 MB ldf file.

Personally I prefer to have model in simple recovery since I feel that is a better default recovey model. An alternative is to regurarly set model in simple recovery and back to full recovery (schduled job).

Table restore and filegroups

TiborKaraszi — Thu, 25 Jun 2009 10:49:00 GMT

The story usually goes something like:

Q - How can I restore only this table?
A - Put it on its own filegroup and you can do filegroup level backup and restore.

The problem with above answer is that it most likely misses the point. We need to ask ourselves:
Why do you want to do a table level restore?

The answer to the question is very often that the table need to be reverted to an earlier point in time, possibly because some accident happened; like deleting all rows in the table by mistake. (See my minimizing data loss when accidents happens article for a more general discussion.) So, why is not filegroup backup that usable for this scenario?

SQL Server will not let you into a database where different data is from different points in time!
(2005+, Enterprise Edition and Developer Edition, has online restore which allow you into the database but you wont be able to access the restored data until you make it current - so it doesn't really changes the basic issue here.)

Now, think about above. If we restore the filegroup backup containing the emptied table, but then need to restore all subsequent log backups up to "now", what good did this song-and-dance-act do us? No good at all (except for a learning experience, of course).
We can of course restore the primary filegroup and the one with the damaged data into a new temp database - to the desired earlier point in time, and then copy the relevant data from this temp database into the production database. But this operation is certainly not as straight forward as just restoring the filegroup backup into the production/source database.

Now, about having data from different point in time (regardless of how you achieve it): Handle with care. Just think about relationship and dependencies you have inside a database. Reverting some table to an earlier point in time can cause havoc for those dependencies.

I won't get into details about how filegroup backups work, online restore, the PARTIAL option of the restore command etc. - you can read about all that in Books Online. The point about this blog is to have somewhere I can point to when I see the "put-the-table-on-its-own-filegroup-and-backup-that-filegroup" recommendation.

As usual, I have a TSQL script to display my points. If you happen to think that it *is* possible to restore part of the database to an earlier point in time into the production/source database - I'm all ears. You can post a comment here, I will be notified. Please use below script as a template, and modify so that we can execute it and re-execute it.
The usual disclaimer is to not execute below if you don't understand what it is doing, etc.

--Drop and create the database 
USE master 
IF DB_ID('fgr') IS NOT NULL DROP DATABASE fgr 
GO 
--Three filegroups 
CREATE DATABASE fgr ON  PRIMARY  
( NAME = N'fgr', FILENAME = 'C:\fgr.mdf'),  
 FILEGROUP fg1  
( NAME = N'fg1', FILENAME = 'C:\fg1.ndf'),  
 FILEGROUP fg2  
( NAME = N'fg2', FILENAME = 'C:\fg2.ndf') 
 LOG ON  
( NAME = N'fgr_log', FILENAME = 'C:\fgr_log.ldf') 
GO 
ALTER DATABASE fgr SET RECOVERY FULL 

--Base backup 
BACKUP DATABASE fgr TO DISK = 'C:\fgr.bak' WITH INIT 
GO 

--One table on each filegroup 
CREATE TABLE fgr..t_primary(c1 INT) ON "PRIMARY"
CREATE TABLE fgr..t_fg1(c1 INT) ON fg1 
CREATE TABLE fgr..t_fg2(c1 INT) ON fg2 

--Insert data into each table 
INSERT INTO fgr..t_primary(c1) VALUES(1) 
INSERT INTO fgr..t_fg1(c1) VALUES(1) 
INSERT INTO fgr..t_fg2(c1) VALUES(1) 

BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH INIT --1 

--Filegroup backup of fg2 
BACKUP DATABASE fgr FILEGROUP = 'fg2' TO DISK = 'C:\fgr_fg2.bak' WITH INIT 

BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH NOINIT --2 

--Delete from t_fg2 
--Ths is our accident which we want to rollback!!! 
DELETE FROM fgr..t_fg2 

BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH NOINIT --3 

--Now, try to restore that filegroup to previos point in time 
RESTORE DATABASE fgr FILEGROUP = 'fg2' FROM DISK = 'C:\fgr_fg2.bak' 
GO 

SELECT * FROM fgr..t_fg2 --error 8653 
GO 

--If we are on 2005+ and EE or Dev Ed, the restore can be online 
--This means that rest of the database is accessible during the restore 
INSERT INTO fgr..t_fg1(c1) VALUES(2) 
SELECT * FROM fgr..t_fg1 

--We must restore *all* log backups since that db backup 
RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = 2 --out of 3 
RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = 3 --out of 3 
GO 

SELECT * FROM fgr..t_fg2 --Success 
--We didn't get to the data before the accidental DELETE! 
GO 


---------------------------------------------------------------------------- 
--What we can do is restore into a new database instead, 
--to an earlier point in time. 
--We need the PRIMARY filegroup and whatever more we want to access 
---------------------------------------------------------------------------- 
IF DB_ID('fgr_tmp') IS NOT NULL DROP DATABASE fgr_tmp 
GO 
RESTORE DATABASE fgr_tmp FILEGROUP = 'PRIMARY' FROM DISK = 'C:\fgr.bak' 
WITH 
 MOVE 'fgr' TO 'C:\fgr_tmp.mdf' 
,MOVE 'fg2' TO 'C:\fg2_tmp.ndf' 
,MOVE 'fgr_log' TO 'C:\fgr_tmp_log.ldf' 
,PARTIAL, NORECOVERY 

RESTORE DATABASE fgr_tmp FILEGROUP = 'fg2' FROM DISK = 'C:\fgr_fg2.bak' 

RESTORE LOG fgr_tmp FROM DISK = 'c:\fgr.trn' WITH FILE = 1, NORECOVERY 
RESTORE LOG fgr_tmp FROM DISK = 'c:\fgr.trn' WITH FILE = 2, RECOVERY 

--Now the data in PRIMARY and fg2 is accessible 
SELECT * FROM fgr_tmp..t_fg2 

--We can use above to import to our production db: 
INSERT INTO fgr..t_fg2(c1) 
SELECT c1 FROM fgr_tmp..t_fg2 

--And now the data is there again :-) 
SELECT * FROM fgr..t_fg2