backup/restore single filegroup

[rustyd]

I am investigating the use of filegroups in a sql server 2k db. I created a test db with 2 filegroups (Primary and TestFG) and 2 tables. One table is in the Primary filegroup and the other is in the TestFG filegroup. I then executed separate sql statements to perform the backups of the filegroups and the log file to separate files:

 

USE master

BACKUP DATABASE TestFileGroup
  FILE = 'TestFileGroup_Data', 
  FILEGROUP = 'PRIMARY'
  TO disk ='C:\a\TestFG_Pri.mdf'

BACKUP DATABASE TestFileGroup
  FILE = 'Testfg_1', 
  FILEGROUP = 'testfg'
  TO disk ='C:\a\TestFG_1.mdf'

BACKUP LOG TestFileGroup
  TO disk = 'C:\a\TestFG_log.log'

 

The statements execute successfully and I then added some additional data to each row and attempted to restore the TestFG filegroup:

 

USE master

RESTORE DATABASE TestFileGroup
  FILE = 'TestFG_1',
  FILEGROUP = 'testfg'
  FROM DISK = 'C:\a\TestFG_1.mdf'
  WITH noRECOVERY

RESTORE LOG TestFileGroup
  FROM disk = 'C:\a\TestFG_log.log'
  WITH noRECOVERY

 

The RESTORE statement returns the following output:

 

The tail of the log for database 'TestFileGroup' has not been backed up. 
Back up the log and rerun the RESTORE statement specifying the FILE clause.

 

My question is that I have backed up the log, am I missing something?

[PlausiblyDamp]

When it comes to doing the restore, before you restore the backups you need to backup the remaining log entries with code like

BACKUP LOG TestFileGroup
  TO disk = 'C:\a\TestFG_log2.log'
   WITH NO_TRUNCATE    --this bit is important

 

Then when you do the restore you will need to add the command

RESTORE LOG TestFileGroup
  FROM disk = 'C:\a\TestFG_log2.log'
  WITH RECOVERY

this will restore the DB including the final change. If you wanted to restore to the point before the final update you will still need to execute the

BACKUP LOG TestFileGroup
  TO disk = 'C:\a\TestFG_log2.log'
   WITH NO_TRUNCATE    --this bit is important

bit, but then change your original restore code to

RESTORE DATABASE TestFileGroup
  FILE = 'TestFG_1',
  FILEGROUP = 'testfg'
  FROM DISK = 'C:\a\TestFG_1.mdf'
  WITH noRECOVERY

RESTORE LOG TestFileGroup
  FROM disk = 'C:\a\TestFG_log.log'
  WITH RECOVERY

Edited January 9, 2006 by PlausiblyDamp

[rustyd]

Still having problems

 

Thanks for the quick response PlausiblyDamp. I tried what you said and I think I'm still missing something.

 

I've included the statements I ran. Do they look correct? Am I supposed to backup the log when I backup the filegroups, then backup the log again before attempting to restore? Your help is greatly appreciated.

 

Here are my results:

 

Backup each filegroup separately. Filegroup testfg is the filegroup I will attempt to restore.

BACKUP DATABASE TestFileGroup
  FILE = 'TestFileGroup_Data', 
  FILEGROUP = 'PRIMARY'
  TO disk ='C:\a\TestFG_Pri.mdf'

BACKUP DATABASE TestFileGroup
  FILE = 'Testfg_1', 
  FILEGROUP = 'testfg'
  TO disk ='C:\a\TestFG_1.mdf'

BACKUP LOG TestFileGroup
  TO disk = 'C:\a\TestFG_log.log'

 

I then make changes to both tables and restore the filegroup containing table 2 (testfg). I start by backing up the transaction log file again to a different file using the 'WITH NO_TRUNCATE' clause:

 

USE master
GO

BACKUP LOG TestFileGroup
  TO disk = 'C:\a\TestFG_log2.log'
   WITH NO_TRUNCATE    --this bit is important

RESTORE DATABASE TestFileGroup
  FILE = 'TestFG_1',
  FILEGROUP = 'testfg'
  FROM DISK = 'C:\a\TestFG_1.mdf'
  WITH NORECOVERY

RESTORE LOG TestFileGroup
  FROM disk = 'C:\a\TestFG_log2.log'
  WITH RECOVERY

 

and I get the following messages

Processed 1 pages for database 'TestFileGroup', file 'TestFileGroup_Log' on file 3.
BACKUP LOG successfully processed 1 pages in 0.407 seconds (0.005 MB/sec).
Server: Msg 3159, Level 16, State 1, Line 6
The tail of the log for database 'TestFileGroup' has not been backed up. 
Back up the log and rerun the RESTORE statement specifying the FILE clause.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.
Server: Msg 4306, Level 16, State 1, Line 12
The preceding restore operation did not specify WITH NORECOVERY 
or WITH STANDBY. Restart the restore sequence, specifying WITH 
NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 12
RESTORE LOG is terminating abnormally.

Edited September 14, 2004 by rustyd

[PlausiblyDamp]

When you backup both file groups to begin you also backup the log - this will cover all the data and clear all commited transactions from the log.

If you make any further changes then these will be commited to the main database but will also exist in the log (this is the tail refered to in the error message).

If you wish to restore the backup over the existing DB then this tail (remaining log) not being backed up causes the error, so you need to back this up using the NO_TRUNCATE option - this marks it as being backed up and doesn't remove it.

Then you would restore each off the filegroups in turn specifying NORECOVERY - this prevents SQL from activating the DB and applying any remaining transactions from the logs.

 

Now depending on if you want to restore up to the point after the first log backup but before the second, or to the point after the second you have two options.

 

For the first case you could now restore the first log and specify RECOVERY and the DB should now be accesible.

For the second case restore the first log specifying NORECOVERY and then restore the second log specifying RECOVERY - all updates should now be recovered.

[rustyd]

Can I restore a single filegroup if I backup multiple filegroups? I haven't been successful yet.

 

I backup both filegroups and the log. I make changes to each of my two tables, both in a separate filegroup.

 

I then Backup the log with changes using the WITH NO_TRUNCATE clause, which succeeds. Next, I Restore the non-primary filegroup using WITH NORECOVERY which also succeeds, but when I restore the log file I get error 4305 (The log in this backup set begins at LSN %.*ls, which is too late to apply to the database. An earlier log backup that includes LSN %.*ls can be restored). I am restoring the backup log that was successful right before the RESTORE.

 

I can restore the PRIMARY filegroup and the log backed up while backing up the filegroups successfully, but when I reconnect to Enterprise Mgr, my database is grey and says it's loading.

 

This is why I ask if I can restore a single filegroup.

[rustyd]

Restore, but results not as expected

 

PlausiblyDamp, after re-reading your last post, I was able to successfully restore 1 of the filegroups followed by the original backup log file and the log file with NO_TRUNCATE.

 

The results I got were not what I expected though. I have 2 tables. I backup both tables, each in a separate filegroup. I then add a record to each table. I then restore the filegroup in table b and both log files (so I all transactions are included). I then view the table a, which was not restored, and it still has the new record.

 

How can I restore so that I get the new record in table b, but table a does not have it's new record?

 

Thanks.

[PlausiblyDamp]

If you don't want the final update don't restore the last log backup - only restore the 1st log and specify recovery as an option.

[rustyd]

Thanks for the quick response. I tried that and I got none of the changes I made to either table.

 

 

Here is what I want:

Pre backup:

Filegroup a Filegroup b

Table a table b

line 1 line 1

line 2 line 2

 

Backup both filegroups

 

Add records to each filegroup:

Filegroup a Filegroup b

Table a table b

line 1 line 1

line 2 line 2

line 3 line 3

 

Restore filegroup b so table b line 3 is included, but line 3 filegroup a is not:

Filegroup a Filegroup b

Table a table b

line 1 line 1

line 2 line 2

line 3

 

That is my goal.

 

Here's my order of operation:

 

BACKUP FILEGROUP a

BACKUP FILEGROUP b

BACKUP LOG complete

 

Add a record to each filegroup

 

scenario a:

BACKUP LOG includesalltransactions (NO_TRUNCATE)

RESTORE DATABASE FILEGROUP a (WITH NORECOVERY)

RESTORE DATABASE FILEGROUP b (WITH NORECOVERY)

RESTORE LOG complete (WITH RECOVERY)

both filegroups to original state

 

 

scenario b:

BACKUP LOG includesalltransactions (NO_TRUNCATE)

RESTORE DATABASE FILEGROUP a (WITH NORECOVERY)

RESTORE DATABASE FILEGROUP b (WITH NORECOVERY)

RESTORE LOG complete (WITH NORECOVERY)

RESTORE LOG includealltransactions (WITH RECOVERY)

both filegroups have both records

 

scenario b:

BACKUP DATABASE FILEGROUP b (re-backed up to save new rec)

BACKUP LOG includesalltransactions (NO_TRUNCATE)

RESTORE DATABASE FILEGROUP a (WITH NORECOVERY)

RESTORE DATABASE FILEGROUP b (WITH NORECOVERY)

RESTORE LOG complete (WITH RECOVERY) (eliminate line 3 in filegroup a)

I get the 4318 error (File 'TestFG_1' has been rolled forward to LSN ... which is too early to apply the WITH RECOVERY option. Reissue the RESTORE LOG statement WITH NORECOVERY)

[rustyd]

When I specify scenario 3 as NORECOVERY, the command is successful, but the database is not as it is greyed out (Enterprise Mgr) saying it is loading.

[PlausiblyDamp]

You need to specify RECOVERY for the last file restored - until you do it will be 'waiting' for another backup to be restored.

If it doesn't work reply and I'll have a try tomorrow - will have access to SQL that can be abused then ;)

[rustyd]

Thanks, here is my data scripts

 

Thanks for all of the help PlausiblyDamp. I'm uploading my sql scripts of the small db I've been testing on.

TestFileGroup_Generate.txt

TestFileGroup_Data.txt

TestFileGroup_Backup.txt

TestFileGroup_Restore.txt

[PlausiblyDamp]

Ran the scripts, created the DB, inserted the data.

Ran the backup scripts (both files + log), inserted new data, backed up log with no_truncate.

 

Restored two main files + first log with RECOVERY and data contained the initial 6 rows, nothing else.

 

Again restored two main files + 1st log all norecovery, then final log with RECOVERY and all 8 rows were present.

 

Seems to work as I would expect.

[rustyd]

Thanks for the help PlausiblyDamp. That is the result I got. I was looking for a way to restore just one of the filegroups so there are 6 records in one table and 8 records in the other? That is what I was trying to achieve. Can you restore a single filegroup?

 

The scenario is we have a networked application. If a user is not on the network, they can connect to a local msde. To get the network data to the local, I want to restore a filegroup with all of the main tables of the network db to the local db. There is one table that stores the individual parameters such as printer settings, report options, screen settings and preferences. The network settings and the local settings can be different, mainly for printer settings. I want to copy all of the network data accept the individual parameters table. If the restore can't do what I want, I can always DTS it, but I was exploring other options.

[PlausiblyDamp]

Sorry - i was being thick and not understanding your question. As far as I'm aware you could restore a single file or filegroup but this is really only intended for when you have suffered a physical loss of the data.

If you want to restore to a particular update you want to investigate RESTORE LOG and the STOPAT option (or even the STOPATMARK / STOPBEFOREMARK)