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
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
You can use the import/export feature. Filter your data based on your tenants. The process can be automated using SSIS.