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.


NB: Database : Microsoft SQL SERVER 2008

2 Answers 2


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.
    – coddey
    Apr 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.

  • 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, 2012 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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