Can individual filegroups be restored separately?

2016-04-15 · by Hannah Vernon · in recovery

In this recent answer to a question on dba.stackexchange.com regarding the benefits of read-only filegroups, I posited that one might want to restore individual filegroups to enable a piece-meal approach to disaster recovery that allows the critical business data stored in one filegroup to be brought online very quickly, while still allowing other non-critical filegroups to be restored while the database is servicing business users.

In order to show how this works, I’ve created some test-bed code describing the steps necessary to complete this type of restore process.

First, we create a dummy database, named “fgRestoreTest”. The database will contain two filegroups, fg1 and fg2.:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'fgRestoreTest')
DROP DATABASE fgRestoreTest;
CREATE DATABASE fgRestoreTest;
GO
ALTER DATABASE fgRestoreTest SET RECOVERY FULL;
BACKUP DATABASE fgRestoreTest TO DISK = 'NUL:'; 
ALTER DATABASE fgRestoreTest ADD FILEGROUP fg1;
ALTER DATABASE fgRestoreTest ADD FILEGROUP fg2;
ALTER DATABASE fgRestoreTest ADD FILE (
    NAME = 'fg1_file1'
    , FILENAME = 'C:\SQLServer\Data\fgRestoreTest_fg1_file1.mdf'
) TO FILEGROUP fg1;
ALTER DATABASE fgRestoreTest ADD FILE (
    NAME = 'fg2_file1'
    , FILENAME = 'C:\SQLServer\Data\fgRestoreTest_fg2_file1.mdf'
) TO FILEGROUP fg2;
GO

USE master;

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'fgRestoreTest')

DROP DATABASE fgRestoreTest;

CREATE DATABASE fgRestoreTest;

ALTER DATABASE fgRestoreTest SET RECOVERY FULL;

BACKUP DATABASE fgRestoreTest TO DISK = 'NUL:';

ALTER DATABASE fgRestoreTest ADD FILEGROUP fg1;

ALTER DATABASE fgRestoreTest ADD FILEGROUP fg2;

ALTER DATABASE fgRestoreTest ADD FILE (

NAME = 'fg1_file1'

, FILENAME = 'C:\SQLServer\Data\fgRestoreTest_fg1_file1.mdf'

) TO FILEGROUP fg1;

ALTER DATABASE fgRestoreTest ADD FILE (

NAME = 'fg2_file1'

, FILENAME = 'C:\SQLServer\Data\fgRestoreTest_fg2_file1.mdf'

) TO FILEGROUP fg2;

Note the recovery model in the database is set to “FULL”, and we’ve completed a dummy “BACKUP DATABASE” command to ensure we are actually running the database in full recovery.

Now, let’s add some data into each filegroup. For purposes of this demo, we’ll consider fg1 to contain business-critical data, and fg2 to contain data that is only for reporting purposes, and therefore is not as critical. The typical scenario here would be the data on fg2 would be considerably larger than the data on fg1, even though each of our dummy tables only contains a single row.

USE fgRestoreTest;
CREATE TABLE dbo.RestoreTestTable_fg1
(
    RestoreTestTable_ID INT NOT NULL
        CONSTRAINT PK_RestoreTestTable_fg1
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
) ON fg1;
INSERT INTO dbo.RestoreTestTable_fg1 DEFAULT VALUES;
CREATE TABLE dbo.RestoreTestTable_fg2
(
    RestoreTestTable_ID INT NOT NULL
        CONSTRAINT PK_RestoreTestTable_fg2
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
) ON fg2;
INSERT INTO dbo.RestoreTestTable_fg2 DEFAULT VALUES;
GO

USE fgRestoreTest;

CREATE TABLE dbo.RestoreTestTable_fg1

(

RestoreTestTable_ID INT NOT NULL

CONSTRAINT PK_RestoreTestTable_fg1

PRIMARY KEY CLUSTERED

IDENTITY(1,1)

) ON fg1;

INSERT INTO dbo.RestoreTestTable_fg1 DEFAULT VALUES;

CREATE TABLE dbo.RestoreTestTable_fg2

(

RestoreTestTable_ID INT NOT NULL

CONSTRAINT PK_RestoreTestTable_fg2

PRIMARY KEY CLUSTERED

IDENTITY(1,1)

) ON fg2;

INSERT INTO dbo.RestoreTestTable_fg2 DEFAULT VALUES;

Let’s take a backup of the database, then drop that sucker!

USE master;
GO
BACKUP DATABASE fgRestoreTest TO DISK = 'C:\TEMP\fgRestoreTest.bak' WITH INIT;
GO
DROP DATABASE fgRestoreTest;
GO

USE master;

BACKUP DATABASE fgRestoreTest TO DISK = 'C:\TEMP\fgRestoreTest.bak' WITH INIT;

DROP DATABASE fgRestoreTest;

Here’s the juicy bit. We’ll restore the “primary” filegroup, along with the “fg1” filegroup, bringing the database online using the “RECOVERY” option.

RESTORE DATABASE fgRestoreTest 
    FILEGROUP = 'fg1'
    , FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\TEMP\fgRestoreTest.bak'
WITH RECOVERY;
GO

RESTORE DATABASE fgRestoreTest

FILEGROUP = 'fg1'

, FILEGROUP = 'PRIMARY'

FROM DISK = 'C:\TEMP\fgRestoreTest.bak'

WITH RECOVERY;

Notice we’re using the FILEGROUP = 'x' syntax – this is the important part, since it tells SQL Server to only restore those particular filegroups. Note in our case the single backup we took contains all filegroups. Importantly, rollback of transactions that involve fg2 are deferred, since this filegroup is not available. Regular operations can continue, but locks are held by these transactions and log truncation will not occur until the rollback can complete.

Let’s see if we can query the tables in the database:

USE fgRestoreTest;
GO
SELECT 'select #1', *
FROM dbo.RestoreTestTable_fg1;
GO

USE fgRestoreTest;

SELECT 'select #1', *

FROM dbo.RestoreTestTable_fg1;

The above query returns rows:

+------------------+---------------------+
| (No Column Name) | RestoreTestTable_ID |
+------------------+---------------------+
| select #1        | 1                   |
+------------------+---------------------+

However, querying dbo.RestoreTestTable_fg2 returns an error:

SELECT 'select #2', *
FROM dbo.RestoreTestTable_fg2;
GO

SELECT 'select #2', *

FROM dbo.RestoreTestTable_fg2;

Since the filegroup containing dbo.RestoreTestTable_fg2 is not online, the query processor cannot even start generating a plan for the query, resulting in the error.

Msg 8653, Level 16, State 1, Line 1
The query processor is unable to produce a plan for the table or view 'RestoreTestTable_fg2' because the table resides in a filegroup which is not online.

In order to start restoring the fg2 filegroup, we must first take a “tail-of-the-log” backup, by specifying the “NORECOVERY” option as part of the “BACKUP LOG” command. Then we need to immediately start restoring the fg2 filegroup, using the “NORECOVERY” option, so we can then restore the tail-of-the-log backup. The restore of the log backup uses the “RECOVERY” option to allow SQL Server to run the recovery process on any outstanding transactions that might affect objects stored in all filegroups on the database:

USE master;
BACKUP LOG fgRestoreTest TO DISK = 'C:\temp\fgRestoreTest_logtail.bak' WITH NORECOVERY, INIT;
GO
RESTORE DATABASE fgRestoreTest 
FILEGROUP = 'fg2'
, FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\TEMP\fgRestoreTest.bak'
WITH NORECOVERY;
RESTORE LOG fgRestoreTest
FROM DISK = 'C:\temp\fgRestoreTest_logtail.bak'
WITH RECOVERY;

USE master;

BACKUP LOG fgRestoreTest TO DISK = 'C:\temp\fgRestoreTest_logtail.bak' WITH NORECOVERY, INIT;

RESTORE DATABASE fgRestoreTest

FILEGROUP = 'fg2'

, FILEGROUP = 'PRIMARY'

FROM DISK = 'C:\TEMP\fgRestoreTest.bak'

WITH NORECOVERY;

RESTORE LOG fgRestoreTest

FROM DISK = 'C:\temp\fgRestoreTest_logtail.bak'

WITH RECOVERY;

Any ongoing transactions at the time of the initial backup (prior to the disaster) that spanned both the fg1 and fg2 database will be rolled-back or rolled-forward, as appropriate.

Once the above restores complete, we’re able to successfully query tables in both fg1 and fg2:

USE fgRestoreTest;
GO
SELECT 'select #3', *
FROM dbo.RestoreTestTable_fg1;
GO
SELECT 'select #4', *
FROM dbo.RestoreTestTable_fg2;
GO

USE fgRestoreTest;

SELECT 'select #3', *

FROM dbo.RestoreTestTable_fg1;

SELECT 'select #4', *

FROM dbo.RestoreTestTable_fg2;

The results:

+------------------+---------------------+
| (No column name) | RestoreTestTable_ID |
+------------------+---------------------+
| select #3        | 1                   |
+------------------+---------------------+

+------------------+---------------------+
| (No column name) | RestoreTestTable_ID |
+------------------+---------------------+
| select #4        | 1                   |
+------------------+---------------------+

Success!

See the following MSDN resources for further details about this process:

Piecemeal Restores (SQL Server)
Example: Piecemeal Restore of Database (Full Recovery Model)

Can individual filegroups be restored separately?

Related

Leave a ReplyCancel reply

Search

Categories

Pages

Meta