THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Table restore and filegroups

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 INTON "PRIMARY"
CREATE TABLE fgr..t_fg1(c1 INTON fg1
CREATE TABLE fgr..t_fg2(c1 INTON fg2

--Insert data into each table
INSERT INTO fgr..t_primary(c1VALUES(1)
INSERT INTO fgr..t_fg1(c1VALUES(1)
INSERT INTO fgr..t_fg2(c1VALUES(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(c1VALUES(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 = --out of 3
RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = --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'
,PARTIALNORECOVERY

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

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

--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
Published Thursday, June 25, 2009 1:49 PM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AllenMWhite said:

Tibor, in my last job as a DBA I had a set of databases controlling the graphical presentation of websites.  The web designers too frequently deleted objects in a website (deleting the table data supporting those objects), realizing later they were in the wrong website, and asked me to restore the table data.

The way I ended up resolving this was by creating a snapshot database for each one of these databases, one per day using the day of the week as the snapshot database name suffix.  Then the next week I'd delete the previous week's snapshot and create the new one.

This way, if on Tuesday the designer deleted data incorrectly and then told me about it on Thursday, I could select the deleted rows from Tuesday morning's snapshot and insert it into the current table.

Yes, I built scripts to automate the daily creation of the databases, and it is still working well in that environment.

Allen

June 25, 2009 12:42 PM
 

TiborKaraszi said:

Allen,

Did you use powershell? Sorry, I couldn't help myself. I *just* finished reading your WP on PS  - Good Stuff, some 2 minutes before I got email on your comment here... :-)

Interesting usage of Db Snapshot. I think we often overlook these simple-but-perhaps-not-obvious way of acheieving something. Sometimes I think that SQL Server is so stuffed with functionality and features so it is easy see only trees...

June 25, 2009 12:53 PM
 

Sankar Reddy said:

Allen,

Interesting approach but the only complaint I have is, SQL Server flushes the entire procedure when you drop a snapshot in 2005, corrected in 2008. It may NOT be a big problem in all environments but yes its a problem for some.

June 25, 2009 3:47 PM
 

AllenMWhite said:

Tibor, I created that solution before Microsoft introduced PowerShell, so it was done with VB/SMO and TSQL.  I'm glad you liked the WP. Thanks.

Sankar, in the environment for that application clearing the proc cache at 5am doesn't cause a problem, but that is an important consideration.

Allen

June 26, 2009 7:42 AM
 

Anwar said:

Allen, what if you couldn't make a snapshot because the size of a VLDB. Is the trigger or replication the best way to maintain a backup of small group of tables? I don't like very much the filegroups way :-(

July 6, 2009 3:36 PM
 

Weam Muhsen said:

Dear Sir,

Can I restore just one table from database backup

July 22, 2009 3:46 AM
 

TiborKaraszi said:

The most granular level of restore is the filegroup level. So, yes, you can restore a single table assumiung that is is alone on a separate filegroup. This should not be done into an existing database, unless you want to forward this table restore into current point in time.

July 22, 2009 4:18 AM
 

Rajnish said:

Very nice article to understand filegroup backup/restore

November 3, 2009 5:48 AM
 

Shoeless_Mike said:

Thanks for the info - I had posted on the SQL Server forum hosted by MS and you pointed me here. This makes complete sense and I am a bit embarrased that I did not figure this out. I have extensive Informix database experience and used to work in their advanced support group. We fielded calls all the time re. table restores and also had to hold customer's hands doing the restore, unload, restore again, roll forward operation - a bit different with Informix back then, but esentially the same idea. Not sure what inspired me to think that SQL would allow data from diff. points in time to exist... my only excuse to offer is that my brain is cluttered with information as I prepare for the 70-432 exam coming up. Again thanks for the info.

MM

March 2, 2010 11:41 AM
 

TiborKaraszi said:

Glad you found it useful, Mike. No need to be embarrased. Sometimes, it is enoough with a nudge to get the picture - then one immediately realizes how obvious the issue is. I can imagine such support cases - regardless of whether the product in question supports restore with various times or if one do it yourself using a separate database and import (or similar). Good luck with the test! :)

March 2, 2010 11:55 AM
 

Martin Van Den Bossche said:

We have a situation here where we are running out of space for the many "one off" environments we have, supporting an "ERP" gradebook system.  This particular piece of software doesn't have a single foreign key in the DB, in fact the DB schema is more akin to a flat file.  Right now we are in the second half of our school year and the full backup is 280GB, the DB size is 470GB.  We have moved several of the "Backup" tables into their own filegroup, and we have moved the table with pdf snapshots of reportcards into its own filegroup.  When we backup the primary filegroup the backup is reduced to ~80GB which makes the copy and restore a lot easier.  However I need to be able to bring the entire DB online after the partial restore to enable the application to "dump" new reportcards into the table.  We do not need the data that was in the table previously, in fact as part of my restore script I truncate the table in question.  Is it possible to accomodate this in SQL 2005?  Is there a backdoor way to bring a filegroup online regardless of LSN if the data is truncated anyway?

With regard,

Martin

March 23, 2010 5:31 PM
 

TiborKaraszi said:

Martin,

YOu can't make this filegroup a "normal" filegroup. Either move your data into a new database and take it from there (including re-think your backup strategy), or live with a defunct filegroup:

http://msdn.microsoft.com/en-us/library/055f9c6a-5c18-4942-98e7-ec918f0ff975.aspx

March 24, 2010 2:10 AM
 

Martin Van Den Bossche said:

We have tested, in a one off environment, moving the larger tables into a different DB and using distributed views to link to the tables in the separate DB.  All worked, however, since this isn't a homegrown app we can't make the change in production without approval from the vendor.  As of yet, we have not heard back from them if they will infact support that particular configuration.  As for the different backup strategy, I would like to deviate from the typical, in our case every 15 min tlog backup, and a daily full; however, I have not been successful in convincing the "powers that be" to stray from the norm.

Nonetheless, thank you for your response.

M

March 24, 2010 9:38 AM
 

DBAGRP said:

Tibor,

I have a database that has primary and FG1.

Backup steps:

I have a full backup followed by couple of log backups, then a Primary FileGroup Backup, followed by a DIFFERENTIAL backup.

I tried to restore the full backup to a new database with no recovery - success

Then I tried to do a differential restore and it failed:

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

I had issued only one full backup prior to the diff backup. Any idea why I am getting the above error? I am sorry if I am posting this on the wrong blog...

Thanks

D

January 11, 2012 2:50 PM
 

Kurt said:

Hi, I tested the scripts on SQL Server 2012 (version:11.0.2100.60 Enterprise Evaluation Edition).

but even I restored LOG backup as below

--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

I still can not visit table fgr..t_fg2, get error msg 8653. I don't why, because the 'Evaluation Edition' ?

May 24, 2012 2:34 AM
 

TiborKaraszi said:

Kurt,

It seems like 2012 is a bit pickier than earlier versions. I.e, after restore "3", you need to produce a new log backup and then restore it. So, add:

backup log fgr to DISK = 'r:\fgr.trn'

RESTORE LOG fgr FROM DISK = 'r:\fgr.trn' WITH FILE = 4

SELECT * FROM fgr..t_fg2 --Success

May 24, 2012 10:46 AM
 

Kurt said:

Tibor,

Thank you for your quick response!

May 24, 2012 11:13 PM
 

Dinesh Vishe said:

If I want Take backup of 2-3 tables .It is possinle. ????

July 2, 2012 2:45 AM
 

TiborKaraszi said:

There is no functionality to backup only a few tables in the backup command. You can use something like BCP or similar to get the data out and script the table definitions.

July 2, 2012 4:39 AM
 

TheDood said:

Just investigated this approach for staging area for a warehouse load. We need a consistent set of data in stage for all source systems and if 1 fails we don't want to hold up the warehouse loads for the other data. So we need to recover data (last successful) relating to specific source system. Without going into loads of detail but architecturally for fast loading warehouse processes we can't use things like snapshots and or anything that requires anything but the simple transaction logging. Bulk logging is an option but I'd rather not have to manage the transaction log for a bulk load staging area of a warehouse load.

Seems the simplest thing we can do is a back up and restore process and then make this even more efficient by splitting the source system into their own stage DB's and just recover the one's that fail (otherwise we have to reload all the others that didn't fail). This is not too bad because we're conforming data using an MDM approach and transforms will run higher up the process stream. The copying into different objects is an option but it means landing the data twice which is not a nice and efficient architecture.

The other idea is using a flip flop idea between 2 stage DB's and repointing the DB connections in the ETL or db name switching. This option avoids landing twice also. Though the downside is if it fails on 2 successive loads we lose the data consistency. So entirely dependant on how well it's supported. I would hope that a failure is investigated and fixed within the day, though we can't guarantee that in an ideal world.

July 30, 2012 6:54 AM
 

X Y said:

It is really great article.

Thanks

June 25, 2013 9:49 AM
 

X Y said:

As Posted by TiborKaraszi said: on May 24, 2012 10:46 AM

I have executed following statement as mentioned.

RESTORE LOG fgr FROM DISK = 'r:\fgr.trn' WITH FILE = 4

It throws following errors:

Msg 3117, Level 16, State 1, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Kindly help me.

June 25, 2013 9:52 AM
 

TiborKaraszi said:

I'm sorry, K Y, but that is not what I am getting. Without being able to repro your situation, I can't say what is going on at your side... Consider creating a script with exactly what you are doing and ask at some of the forums out there, those are better suited for getting help...

June 26, 2013 3:30 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement