Separating an SQL Server database

Question

I'm using SQL Server 2008. My database is almost 2GB in size. 90% of it is one table (as per sp_spaceused), that I need don't for most of my work.

I was wondering if it was possible to take this table, and have it backed up in a separate file, allowing me to transfer the important data on a more frequent basis than this one.

My guess is the easiest way to do this is create a new database, create the table there, copy the table contents to the new database, drop the table relationships, drop the table, create a view pointing to the other database and use that view in my applications.

However, I was wondering if you had any pointers to different strategies that I may not be aware of at this point.

Answer 1

Create the table in a different FileGroup.

Here's a link with some good examples.

This creates a second physical file for just that table. It can be placed on a different physical drive for performance. You can do a backup or restore of just specific filegroups, which is what it sounds like you need.

This is one example of the larger topic of "Data Partitioning", which involves various methods of dividing large tables across multiple files.

Answer 2

I suggest the filegroup solution. However to copy a table from a database to another you can do this trick:

SELECT * INTO MyNewDatabase..MyTable FROM MyOldDatabase..MyTable