See Also: Main_Page - Database Administration - Backup & Recovery

Filegroup Restores

With small to medium databases backup and restore operations are straightforward. You can generate full, differential and transaction log backups and restore them in the sequence they were taken. When you have a terabyte caliber database you face a different scenario because restoring a full backup can take many hours. While the database is being restored, no user can connect to the database. This can be particularly frustrating when the majority of your data is static and only a small portion is updated daily. Fortunately SQL Server 2005 Enterprise Edition (and later editions) provides an answer to this challenge through piecemeal restore.

Previous versions of SQL Server allowed database administrators to backup individual files and file groups instead of backing up the entire database. However, before the database becomes available you must restore every file and file group. This solution allows reducing the total backup time, but doesn't help with database availability.

Filegroup Restore Tutorial Video

In this ten minute video, we show how restoring filegroups is different from SQL 2000 to 2005 by performing the restores and showing how the database reacts.

http://tutorials.sqlserverpedia.com/SQLServerPedia-20090126-FilegroupRestore.flv

For the scripts shown in the tutorial, check out the Filegroup Restore Script article.

For more tutorial videos like this, check out the SQL Server Tutorials page.

Get Online Faster When Disaster Strikes

With SQL Server 2005, the database can be available for querying after its PRIMARY file group has been restored. You can continue restoring other files and file groups while users are querying the database. Only the portion of the database being restored will not be available to the users. In fact, if you don't have the backup files immediately available or if you don't want to impose any extra overhead on the system, you can wait as long as you want before restoring the rest of the file groups. The file groups that haven't been restored remain in offline state.

For example, suppose the northwind database has two file groups: primary and secondary. Further suppose that the primary file group contains the majority of data users are interested in. The secondary file group was added recently and doesn't contain much data. You have two full backups - one for primary file group and one for secondary. The primary file group contains two data files and the secondary file group contains a single data file. For simplicity sake, assume that you only take full backups (no differential or transaction log backups). To restore just the primary file group, execute the following statement (you only have to specify MOVE clause if you wish to alter location of data files and log files):

RESTORE DATABASE northwind2  FILEGROUP = 'PRIMARY' FROM DISK = 'c:\northwind_primary.bak'  WITH PARTIAL, RECOVERY,  MOVE 'northwind' TO 'c:\northwind_data1.mdf',  MOVE 'northwind_data2' TO 'c:\northwind_data2.ndf',  MOVE 'northwind_log' TO 'c:\northwind_log1.ldf'

At this point northwind2 database is available for reading and writing. If you wanted to restore all read-write file groups as opposed to just the primary file group we could modify the statement slightly:

RESTORE DATABASE northwind2  READ_WRITE_FILEGROUPS FROM DISK = 'c:\northwind_primary.bak'  WITH PARTIAL, RECOVERY

At some point when user activity on the system is minimal, execute a statement similar to the following to restore the secondary file group:

RESTORE DATABASE northwind2  FILEGROUP = 'FG2003H1'  FROM DISK = 'c:\nwnd_secondary_backup.bak'  WITH RECOVERY,  MOVE 'north_data3' TO 'c:\northwind_data3.ndf'

Note that users can continue accessing the database while the secondary file group is being restored.

Plan Ahead: Design Your Filegroups For Faster Restores

Things get more complicated when you want to restore a single filegroup from a backup.

Let’s say we have a 1 terabyte data warehouse. If our SQL Server goes down, we can’t restore 1 terabyte of data fast enough to make sure we don’t get fired, but we can’t afford a hot standby system. SQL Server 2005’s new filegroup backup & restores give me a way around that.

First, long before disaster strikes, we have to break it up into a series of filegroups for easier management:

• 500gb filegroup with old sales data (more than 1 year old, and that data doesn’t change, so we’ve made it read-only)
• 400gb filegroup with old payroll data (also more than 1 year old)
• 100gb primary filegroup with current sales & payroll data (stuff in the last year, and all the current data goes in here)

When disaster strikes, here’s what SQL 2005’s new filegroup restore lets us do:

• Restore the primary filegroup with the current data in a matter of minutes, and put the database online. The users can query, but if they try to query more than a year ago, they’ll get an error message.
• Restore the old sales filegroup while the users are already querying the current data. We want this faster than payroll, because we only do payroll every two weeks, but the sales guys may want to query old sales data faster.
• Restore the old payroll filegroup, bringing the database fully online.

But keep in mind that we’re talking about complete disasters here, like when the server craters altogether and we have to start with a restore of our primary filegroup.

Filegroup Design Tips and Tricks

Since the primary filegroup must come online first, keep the primary filegroup relatively small. If the database is one terabyte, don't create a 900 gigabyte filegroup as the primary file, because filegroup restores won't be much faster than conventional restores.

Create a primary filegroup with the most urgently needed tables: configuration tables, user security, and whatever your application absolutely must have in order to run.

Then create a secondary filegroup with the most commonly queried tables: customers, items, warehouses, employees, whatever data is relatively small and helpful.

Finally, create additional secondary filegroups with large tables that are not queried as frequently, like archived data or reporting tables.

Restoring a Filegroup From a Full Backup

Continuing our example above, let’s say we want to restore the 400gb old payroll filegroup out of a full backup chosen at random, like from last week, without any matching transaction logs to bring that filegroup up to speed. We know it's only old data, and we're sure nothing's changed, and we just want SQL Server to restore it.

That won’t work, but to understand why, we have to zoom back out again to look at other ways filegroups can be configured. Forget the nice, clean breaks that we did in our example above – here’s some other different and completely valid ways to configure multiple filegroups:

Scenario A: Load Balancing Data vs Indexes

• Primary filegroup – has the data in it (the tables)
• Index filegroup – has the indexes in it

If we restored the index filegroup after we’ve been making changes to the primary (data) filegroup, the indexes would be garbage. They would point at records that may not even exist in the primary filegroup, or vice versa – we may have records in the primary filegroup that don’t have matching indexes in the index filegroup.

Scenario B: Load Balancing Types of Tables

• Primary filegroup – has our OrderLineItems table
• Secondary filegroup – has our Orders table

If we restored the secondary filegroup from a full backup without having all the transactions to match, we might have OrderLineItems with no matching Order records.

To eliminate these risks, SQL Server won’t let you pluck a filegroup out of a full backup unless you have the matching log to bring it up to speed with the rest of your database. You might have a perfect design, you might say “I know for sure nothing changed, believe me!” but SQL won’t take your word for it. You either have to have the disaster recovery scenario we talked about earlier (restoring the primary filegroup first, then secondaries) or else you have to have the complete log chain to bring your secondary server up to speed with the rest of the database.

Recommended Reading on Filegroup Restores

• SQL Server 2008 Books Online on Filegroup Restores