Is there a way to only backup a SQL 2005 database structure fully, but only the data in a certain set of schemas?

Question

I have several schemas in my database, and the largest one ("large" meaning disk space consumed) is my "web" schema which is a denormalized copy of data in the operational schemas.

This denormalized data is able to be reconstructed at anytime, and is merely there for extremely fast read purposes. Since the data is redundant, and VERY large - I'd like to exclude it from being backed up. I already have stored procedures that can regenerate all of the data in that schema in a couple of hours - for use in the event of a failure.

I assume I can split the tables in this schema out to another data file or such (ideally even on another drive for faster reads), but is there a way to never have that data file backup, yet still in the event of a failure its structure could be restored (and other DDL stuff like procs, views, etc)?

Somewhat related, can I also have these tables not do transaction logging, if I go to "Full" backup mode for the rest of the database?

Answer 1

To support all the capabilities you desire you will have to move the denormalized data out into a separate database.

Answer 2

Take a look at moving your "large" schema entities into a read-only filegroup (see http://msdn.microsoft.com/en-us/library/ms190257.aspx). This will also avoid the locking/transaction overhead on this data.

You can then use partial backup to only backup the primary filegroup and any optional file groups of your choice (see http://msdn.microsoft.com/en-us/library/ms191539.aspx).