In Multi-tenancy , Shared Database Shared Schema. How to take Tenant level backup and restored the tenant data when required? Can any one guide me or point me to the right direction.
Thanks!!!
NB: Database : Microsoft SQL SERVER 2008
2 Answers
You can't without export/import.
A single, shared database implies all data is linked and requires full transactional/referential integrity. So the standard backup/restore won't work because you can't cleanly (or at all?) do row-level partial actions.
You're export/import needs to be transactional safe too, and foreign keys need considered
To make things simpler, you really need a database per tenant.
See Decision criteria on when to use a non-dbo schema vs a new Database for more discussion
If you can't, then you have to live to export/import
-
thanks for the comment. I am too in the favor of Tenant based database. But I am confuse how to handle authentication authorization if database is isolated, in Single Instance Web application for multiple isolated tenant db.– coddeyApr 18, 2012 at 9:47
You can use the import/export feature. Filter your data based on your tenants. The process can be automated using SSIS.
answered Apr 18, 2012 at 5:33
-
Thanks for the suggestion. But do you think it will be right approach as the number of tenant will be more than 10,000. Taking backup of all the tenant and if required restoring the tenant data say tenant no 267 among 10,000 tenants for more than 150 tables for each tenant.– coddeyApr 18, 2012 at 6:04