We have 2 different SQL Servers which both have a copy of the AdventureWorks2012 database in Full recovery mode.

One of the SQL Server hosts the primary copy of the AdventureWorks2012 database which gets restored every night to the other SQL Server as a secondary copy for development use.

One of the tables in the AdventureWorks2012 grows very quickly but isn’t needed on the secondary copy because its information isn’t needed for development. Since it takes up a lot of space it would increase the restore process if this specific table isn’t restored every night.

One of the developers thinks using Filegroups and Filegroup backups might make it possible to restore only the data that is needed to the secondary copy and leave out the large table.

In this test we will be using the AdventureWorks2012 database provided by Microsoft.

We will be using 2 SQL Server 2012 servers running Windows Server 2008R2.

Building the test environment

On the primary server we restored a fresh copy of the AdventureWorks2012 database.

After the restore the AdventureWorks2012 database only has 1 default filegroup, our first step will be added a secondary filegroup to the database:

1
2
3
4
5

USE [master]
GO
 
ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [ARCHIVE]
GO

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [ARCHIVE]
GO

In this case we named our new filegroup ARCHIVE.

We now want to create a new file in our ARCHIVE filegroup to hold our ‘large’ table:

1
2
3
4
5
6
7
8
9
10
11
12

USE [master]
GO
 
ALTER DATABASE [AdventureWorks2012]
ADD FILE
(
NAME = N'AdventureWorks2012_Archive',
FILENAME = N'D:\Data\AdventureWorks2012_Archive.ndf',
SIZE = 25600KB , FILEGROWTH = 20480KB
)
TO FILEGROUP [ARCHIVE]
GO

USE [master]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE
(
NAME = N'AdventureWorks2012_Archive',
FILENAME = N'D:\Data\AdventureWorks2012_Archive.ndf',
SIZE = 25600KB , FILEGROWTH = 20480KB
)
TO FILEGROUP [ARCHIVE]
GO

Now that we have our filegroups and filegroup files, we want to move the ‘large’ table into the secondary filegroup ARCHIVE.

In this test we will use the Sales.SalesOrderDetail table as our ‘large’ table. However this table has a PRIMARY KEY set on one of its columns, since we cannot move a table with a PRIMARY KEY we will have to remove it for this test, this will also remove the CLUSTERED INDEX on the table:

1
2

ALTER TABLE Sales.SalesOrderDetail
DROP CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID

ALTER TABLE Sales.SalesOrderDetail
DROP CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID

Now that our PRIMARY KEY and CLUSTERED INDEX are removed we can recreate the table into the ARCHIVE filegroup:

1
2
3

CREATE CLUSTERED INDEX IX_Sales_SalesOrderDetail_SalesOrderDetailID
ON Sales.SalesOrderDetail (SalesOrderDetailID)
ON Archive;

CREATE CLUSTERED INDEX IX_Sales_SalesOrderDetail_SalesOrderDetailID
ON Sales.SalesOrderDetail (SalesOrderDetailID)
ON Archive;

If we take a look at the table properties we can see that the table has indeed been moved to the ARCHIVE filegroup:

Now that we have configured our database with our new ARCHIVE filegroup and moved the ‘large’ table to it we can make a backup of the database to restore on our secondary server:

1
2
3
4
5
6
7
8
9
10
11

BACKUP DATABASE [AdventureWorks2012]
TO  DISK = N'D:\Backup\AdventureWorks_FileTest.bak'
WITH
NOFORMAT,
INIT,
NAME = N'AdventureWorks2012-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

BACKUP DATABASE [AdventureWorks2012]
TO  DISK = N'D:\Backup\AdventureWorks_FileTest.bak'
WITH
NOFORMAT,
INIT,
NAME = N'AdventureWorks2012-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

On our secondary SQL Server we will use this backup file to create the AdventureWorks2012 database:

1
2
3
4
5
6
7
8
9
10

USE
[master]
 
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'D:\Backup\AdventureWorks_FileTest.bak'
WITH
FILE = 1,
NOUNLOAD,
STATS = 5
GO

USE
[master]
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'D:\Backup\AdventureWorks_FileTest.bak'
WITH
FILE = 1,
NOUNLOAD,
STATS = 5
GO

Because we won’t be needing the data in the ‘large’ table we will truncate it on our secondary database:

1

TRUNCATE TABLE Sales.SalesOrderDetail

TRUNCATE TABLE Sales.SalesOrderDetail

We now have the situation we want, our primary database has all the information needed for production purposes and our secondary database has an empty Sales.SalesOrderDetail table.

Restoring only one filegroup on a different SQL Server

The first thing to do now that we have our test environment all set up is to add some data to the primary database. This database is in a production environment so it is logical data will be added or modified.

In this case I simply added a few rows to different tables in the AdventureWorks2012 database. It doesn’t matter what you add or change as long as the action gets logged into the Transaction Log.

Now that we have changed some records around in our primary database we will start a filegroup backup of our PRIMARY filegroup since we don’t need the tables we have included in our ARCHIVE filegroup in our secondary database:

1
2
3
4
5
6
7
8
9
10
11

BACKUP DATABASE [AdventureWorks2012]
FILEGROUP = N'PRIMARY' TO  DISK = N'D:\Backup\AdventureWorks_FileTest_Primary.bak'
WITH
NOFORMAT,
INIT,
NAME = N'AdventureWorks2012-Full Filegroup Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

BACKUP DATABASE [AdventureWorks2012]
FILEGROUP = N'PRIMARY' TO  DISK = N'D:\Backup\AdventureWorks_FileTest_Primary.bak'
WITH
NOFORMAT,
INIT,
NAME = N'AdventureWorks2012-Full Filegroup Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

After the filegroup backup was completed I copied it to the secondary SQL Server and started the filegroup restore:

1
2
3
4
5
6
7
8
9

RESTORE DATABASE [AdventureWorks2012]
FILE = N'AdventureWorks2012_Data'
FROM  DISK = N'D:\Backup\AdventureWorks_FileTest_Primary.bak'
WITH
FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

RESTORE DATABASE [AdventureWorks2012]
FILE = N'AdventureWorks2012_Data'
FROM  DISK = N'D:\Backup\AdventureWorks_FileTest_Primary.bak'
WITH
FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

During the restore all will seem well until we run into an error at the end of the restore:

Processed 29752 pages for database ‘AdventureWorks2012′, file ‘AdventureWorks2012_Data’ on file 1.

Processed 2 pages for database ‘AdventureWorks2012′, file ‘AdventureWorks2012_Log’ on file 1.

The database cannot be recovered because the log was not restored.

The roll forward start point is now at log sequence number (LSN) 507000000318400006. Additional roll forward past LSN 507000000318400006 is required to complete the restore sequence. This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous

messages indicate reasons why recovery cannot occur at this point.

RESTORE DATABASE … FILE=<name> successfully processed 29754 pages in 12.415 seconds (18.723 MB/sec).

The reason why this error occurs is because both databases will generate different LSN (Transaction ID’s) for actions that happen in the database. Because we changed some data around in our primary database the last LSN number will be different then the one on the secondary database.

We can try to restore a transaction log backup from the primary database on the secondary, for this I created a Transaction Log backup on the primary SQL Server.

When trying to restore the transaction log backup you will be greeted by the following error:

Msg 4330, Level 16, State 1, Line 1

This backup set cannot be applied because it is on a recovery path that is inconsistent with the database.

The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point.

Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.  For more information about recovery paths, see SQL Server Books Online.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

So even though we try to restore the Transaction Log from the primary database which includes all change to the tables SQL Server won’t let us because the primary and secondary database are not consistent because of the LSN numbers not matching.

Our only way to make the secondary database available again is by restoring a full backup from the primary database. This makes both databases consistent again.

Conclusion

Using filegroups to skip the restore of certain tables on a secondary database will not work because both databases will generate different LSN numbers, creating inconsistencies between both databases.