SQL Server Partial Database Backup (excluding some tables)
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?
Answer
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