I have a single database that I put all tables into with multiple schemas for the different products I have. I know I can't backup and restore just one schema, but if I move all tables for a schema into a separate filegroup can I then back and restore just that schema without affecting other tables in other schemas? I don't have foreign keys between schemas, so there would be no conflicts.
Also, can I restore into a different database just one of the filegroups without restoring the others?
A while back I decided to stick with just one database, thinking there was a way to accomplish this. If there is no way to do this then it is better to create multiple databases.
Best Answer
I don't know of anyway to do a schema backup or restore in SQL Server. Your solution of putting all of the schema data on a filegroup will allow you to restore the database to another server, but will require that you restore the primary filegroup as well. (note, this is possible on SQL Server Enterprise Edition versions 2005 and above). One problem for your situation might be that you could only have one of the partially restored databases running on the same instance of SQL Serve This link covers the piecemeal restore process: http://msdn.microsoft.com/en-us/library/ms177425.aspx