Should I move tables to...a new filegroup + file(s) or a new database?

[TheBugSlayer]

Hi all.

I finally have the permission to do what I think is right...Here is the scenario, in a SQL Server 2005 database I added objects for a new application that wrote. I created a new schema for this app only; this is how I distinguish "my" objects from the plethora of other DBs and other object on the server. At this time my concern is backup/restore. I would like to be able to back up or restore these objects independently as the back file is huge and keeps growing by the day. Reoovery model if FULL but as far as I can tell you cannot restore only a subset of objects from withing a backup file (correct?). In light of the above, I am thinking about:
1- Creating a new database and moving everything under my new schema to it, then setup a separate maintenance plan for it
2- Creating a new database file and a new file group, moving the objects under my schema to it and setting up a new maintenance plan for the new filegroup.

Is there another way to achieve the same? How do the methods compare to each other?

Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[ousoonerjoe]

If it is a separate application with separate data, then yes, I would make it a self-contained database and data file. Create your own maintenance plans and backup/restore processes. Unless all the data is interrelated, there is no need to have just 1 large massive database. Split it out and make them self contained. It's also a protective measure. A single typo can wipe everything versus just a particular dataset.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[TheBugSlayer]

Thanks. I was already leaning towards moving everything to a new database. There is only one table, a states, table that I need from the tables outside my schema. I guess that' s what I' ll do. The told me to "do it this way" at the beginning...event though I knew it would come to this I still did it "this way".

Have a great day.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[ousoonerjoe]

If the only thing that is mutual is a "States" Lookup table, by all means, split it out. The rewards GREATLY out weigh the negatives. In fact, I would push to separate out all independent systems and make them truly stand alone databases. In cases where there is shared core data, ok. Those can stay together. But like in your example where the only "shared" data is a States lookup table.... Create your new database and using the Import/Export tool, copy the States table into your new database. That simple.

It'll make maintenance, updates, version control, security, restores, etc, so much easier to manage.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[TheBugSlayer]

That' s right. The States table only contains about 50 rows...we' re not splitting the country any time soon . It could be in all DBs for all I care. Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)