Backing up a Filegroup Only

Question

Backing up a Filegroup Only

John Burchel

Hall of Fame

Points: 3611
More actions
July 31, 2006 at 4:38 am

#61434

Hi All,

I currently have a huge db, but here is what i would like to do.
Backup a filegroup every month.
Before doing that, I would need to know what i have to do, should I need to restore that filegroup,
Do I first need to restore Master etc, then that filegroup or what ?
As most often, if a restore is needed, I dont need the entire DB, but only the Filegroup that I just backed up.
Thanks
John

Kindest Regards,
John Burchel (Trainee Developer)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I seem to remember that until 2005 you can't selectively restore individual file groups. If your backup window is too large might I suggest you investigate Litespeed, which does exactly what it says on the box,

I assume you data doesn't change very much?

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Lance Sanders SSC Eights! Points: 903 More actions · Answer 2

SQL 2000 most definitely allows you backup/restore individual filegroups. You can even do individual filegroup restores from a full database backup. What I can't seem to make it do is to use filegroup backups to restore to a point in time for the filegroup, unless you're restoring all filegroups as a set (full db restore). When performing filegroup restores, the database must be left in a consistent state by rolling forward all logs since the filegroup backup, including the current log. This makes it primarily useful for disk corruption scenarios, not correcting mistakes with the data.

The steps to restore a filegroup are:

1: Backup the current tran log. If you don't, you'll receive an error indicating such and the restore operation will halt, without affecting the current state of the database.

2: Restore the desired filegroup. It may still fail with the error about backing up the tail of the tran log. If so, backup the log again, to a new file or appending to the most recent file or device, and retry the restore.

3: Restore all tran logs since the filegroup was backed up, except the most recent, specifying norecovery.

4: Restore the most recent tran log with the recovery option.

That's it. A word of caution, applying all the tran logs with norecovery, then simply issueing a "restore log dbname with recovery" will work, but it will cause subsequent recoveries from the same backups & logs to fail.

If you take this approach, I'd recommend filegroup backups much more frequently than once/month, as well as trying to get a full backup whenever the maintenance window allows for it. Also, definitely set up a sandbox to play with this, as it can take some time to become comfortable with the process.

Here's an example. Assuming the database is defined as:

create database DR_TEST 
on 
primary ( name=primary_1, filename='h:\mssql\data\DR_TEST_primary_1.mdf')
, filegroup fg_rolu (name=rolu_1, filename='h:\mssql\data\DR_TEST_rolu_1.ndf'), 
                    (name=rolu_2, filename='h:\mssql\data\DR_TEST_rolu_2.ndf')
, filegroup fg_text (name=text_1, filename='h:\mssql\data\DR_TEST_text_1.ndf'), 
                    (name=text_2, filename='h:\mssql\data\DR_TEST_text_2.ndf')
log on (name=log_1, filename='h:\mssql\data\DR_TEST_log_1.ldf'), 
       (name=log_2, filename='h:\mssql\data\DR_TEST_log_2.ldf')
go

And the following backups have been taken:

backup database DR_TEST to disk='DR_TEST.full.bak'
go
backup log DR_TEST to disk='DR_TEST_1.trn' 
go
backup database DR_TEST filegroup='primary' to disk='DR_TEST_primary.bak' 
go
backup log DR_TEST to disk='DR_TEST_2.trn' 
go
backup database DR_TEST filegroup='fg_rolu' to disk='DR_TEST_rolu.bak' 
go
backup log DR_TEST to disk='DR_TEST_3.trn' 
go
backup database DR_TEST filegroup='fg_text' to disk='DR_TEST_text.bak' 
go
backup log DR_TEST to disk='DR_TEST_4.trn' 
go

Then restoring the "fg_rolu" filegroup would look like this:

checkpoint
go
backup log DR_TEST to disk='DR_TEST_5.trn' 
go
restore database DR_TEST filegroup='fg_rolu' from disk='DR_TEST_rolu.bak' with norecovery
restore log DR_TEST from disk='DR_TEST_3.trn' with norecovery
restore log DR_TEST from disk='DR_TEST_4.trn' with norecovery
restore log DR_TEST from disk='DR_TEST_5.trn' with recovery
go

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

sorry being too vague ( again ) .. yes I intended to say that restoring a filegroup without the other filegroup backups I don't think is allowed in sql 2000 but I believe might be supported in 2005. I figure you'd get an inconsistant database with a part restore, which would be bad - hence my comment about data not changing.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/