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
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 only takes a minute to sign up.Sign up to join this community
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