Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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

share|improve this question

2 Answers

up vote 2 down vote

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

share|improve this answer
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. – coddey Apr 18 '12 at 9:47
up vote 1 down vote

You can use the import/export feature. Filter your data based on your tenants. The process can be automated using SSIS.

share|improve this answer
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. – coddey Apr 18 '12 at 6:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.