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