Link to home
Start Free TrialLog in
Avatar of HBPROCK
HBPROCK

asked on

Can you backup individual schemas within a database?

I have an sql 2008 datbase which contains two data schema's.  The data schema's are two seperate sets of live data which are called from third party application
I need to be able to backup the schema's individual so that if necessary I can restore one data schema set without effecting the other schema
The standard maintenance plan task in SQL 2008 appears to only allow the entire database to be backed up and does not appear to have an option to specify the schemas seperately.
ASKER CERTIFIED SOLUTION
Avatar of Rich Weissler
Rich Weissler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Schemas are logical entities, while the backup operates on a physical level. Technically, you could create tables of the two schemas in different filegroups and then you would be able to back up a single filegroup. However, the database integrity is maintained across filegroups, and if you restore just one filegroup, you will have to roll it forward to the end of logs that are applicable to that filegroup. You cannot have a part of the database (filegroup or otherwise) restored to one point in time, while the rest of the database is at a later point in time.

If you need these schemas to live independent lives, they have to reside in separate databases. You could then use synonyms to refer in one database to the objects in another database.
Avatar of Viperhawke
Viperhawke

It is not possible to backup/restore a schema.
You also can can not partition based on Schema either.
This information was confirmed via MS internal SQL discussion.
What you can do is purchase a reliable backup third party tool such as Red-Gate's SQL Backup (http://www.red-gate.com/products/SQL_Backup/index.htm) which supports restoring specific tables.