Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 1 '12 at 14:08

This question came from our site for professional and enthusiast programmers.

2 Answers 2

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.

share|improve this answer
  • 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...
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.