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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.