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

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/table-restore-and-filegroups/

Published Thursday, June 25, 2009 1:49 PM by TiborKaraszi

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
 

COMPLETE DORK said:

thanx a lot for that article

October 27, 2016 12:47 PM
 

ravi Shukla said:

Tibor

i have tried above SQL script to restore individual file  group but still fg2 filegroup is not coming online getting below error:-

Error :-File is not online so not able to perform any action its similar erro KURT got  on above chat .. but still i am not able figure out .. i have follow below steps   can you please let me know the if i am doing something wrong

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:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\DATA\fgr.mdf'),  

FILEGROUP fg1  

( NAME = N'fg1', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\DATA\fg1.ndf'),  

FILEGROUP fg2  

( NAME = N'fg2', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\DATA\fg2.ndf')

LOG ON  

( NAME = N'fgr_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\DATA\fgr_log.ldf')

GO

ALTER DATABASE fgr SET RECOVERY FULL

--Base backup

BACKUP DATABASE fgr TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\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:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\fgr.trn' WITH INIT --1

--Filegroup backup of fg2

BACKUP DATABASE fgr FILEGROUP = 'fg2' TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\fgr_fg2.bak' WITH INIT

BACKUP LOG fgr TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\fgr.trn' WITH NOINIT --2

--Delete from t_fg2

--Ths is our accident which we want to rollback!!!

DELETE FROM fgr..t_fg2

select *from fgr..t_fg2

BACKUP LOG fgr TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\fgr.trn' WITH NOINIT --3

--Now, try to restore that filegroup to previos point in time

RESTORE DATABASE fgr FILEGROUP = 'fg2' FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\fgr_fg2.bak'

GO

backup log fgr to DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\fgr.trn'

RESTORE LOG fgr FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\\fgr.trn' WITH FILE = 4

SELECT * FROM fgr..t_fg2 --Success

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:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\fgr.trn' WITH FILE = 2 --out of 3

RESTORE LOG fgr FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.RAVI1\MSSQL\Backup\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

July 24, 2017 11:58 AM
 

Ravi Shukla said:

Hello Tibor

I am getting Error below Error while taking individual fiel group backup.

i have followd all the step given by you .

Msg 8653, Level 16, State 1, Line 2

The query processor is unable to produce a plan for the table or view 't_fg2' because the table resides in a filegroup which is not online.

July 25, 2017 3:00 AM
New Comments to this post are disabled
Privacy Statement