Can I physically split a SQL Server database?

Question

I seem to remember some time ago reading an article saying I could split a SQL Server database into more than one file, allow me to elaborate.

I have a database that has a dozen or so tables that reflect statistics for the last 'n' years. The tables are called History2007, History2008 etc and the current year's table is just called History. All the previous years tables contain static data (won't be changed) and I would like to move these files to another file that is linked to the main database. As the old data tables contain foreign keys to Articles, Clients and Suppliers etc I can't just copy these tables to a new database.

The whole idea is to reduce the size of the database and the backups as I can make one backup of the 'old' data and that will not change until next year.

Any thoughts? Ideas? Guidance?

Thanks, Martin.

Answer 1

Yes, is possible to move old data to filegroups that are still part of the database, but store on files located on cheaper, slower, storage, adnd mark these file groups as read-only. You still need to do a full database backup once, but subsequent backups can leave the read-only file groups out of the backup, significantly reducing the size (and time!). I recommend you read Performing Piecemeal Restores.

Another useful feature is partitioning, which allow you to split a table automatically into partitions and place partitions onto this slow, untouched and read-only file groups. See How to Implement an Automatic Sliding Window in a Partitioned Table.

Answer 2

• you may move the data into different DBs
• you may add some historical filegroups/files to your DB, move your historical tables into this filegroups and mark them as readonly, next year you may want to add extra filegroup, move tables there and again mark it as readonly, and again..., and again...