6

I'm managing a reasonably large SQL Server database. Some tables contain data that are business-critical and must be backed up offsite daily. But we also have other (read-write) tables that take up about half the size of the database that aren't business-critical. What I would like to do is something like this:

Primary filegroup: Tables A, B, C --> daily backup
Secondary filegroup: Tables D, E, F --> monthly (or occasional manual) backup

When I tried to test this, I got errors while trying to restore the filegroups. It looks like I can't restore a single filegroup alone or different file groups from different points in time. Ideally, I'd like to be able to just restore the primary filegroup (the most important one) first, and then restore the secondary one. I'm willing to accept some data loss on the secondary filegroup.

Can this be done?

Improve this question
2
  • Are you doinga complete database backup or backing up the filegroups individually? You need to back them up individually to restore them piecemeal. Also suggest to leave PRIMARY just for system objects and create another secondary filegroup. This question should be on dba not so Commented Nov 8, 2012 at 19:25
  • I'm open to trying anything. I'm currently doing complete backups, but the files are getting too large, and that's why I'm trying to move towards partial backups and back up filegropus individually. I have no trouble backing it up, but I can't restore it. Commented Nov 9, 2012 at 1:04

1 Answer 1

Reset to default
4

In order to succeed with a partial or piecemeal restore strategy, you first need to adopt a Filegroup backup strategy. You can still backup your whole database at one time if you wish, but the backup needs to be at the filegroup level.

Details of how to perform filegroup backups can be found at the following link: http://msdn.microsoft.com/en-us/library/ms179401(v=sql.105).aspx

Details of how to perform a piecemeal restore can be found here http://msdn.microsoft.com/en-us/library/ms177425(v=sql.100).aspx

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks Pete. I tried the technique described on those pages, but I keep getting errors. Can you confirm that we can indeed restore filegroups piecemeal from different points in time (per my example in the question)?
There are some considerations, according to the Recovery Model that you are using. Please check this URL: technet.microsoft.com/en-us/library/ms189860.aspx
You have to roll forward the old pieces to match the newest, using the log backups. In the Simple Recovery Model you don need to roll forward the readonly filegroups.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.