Best way to backup specific schema?

Question

I have a multi-tenant database, each user has their own schema.

What is the best way to backup a single tenant (table schema)? As far as I know SQL Server does not support backup of a single schema (only the complete database).

I need to backup the structure and data. Also it needs to be automated (Ideally I should be able to call it from SSMS as well).

I was thinking exporting the ddl and data as sql statements. If there is some way to call the "Generate and Publish Scripts" wizard as stored proc I think it would work?

I am currently on Sql Server 2008 R2 but could upgrade.

Answer 1

A couple of ideas.

Using File Groups

Put the tables each tenant has into their own file group. SQL Server has the ability to backup and restore individual file groups. You can also perform some other operations such as taking indivudual tenants offline if required. For example:

CREATE TABLE tenant1.Table1 
(Column1 INT, Column2, INT)
ON Tenant1FileGroup

Views & Separate Databases

Probably not the right way to go, but it will work. Have the tables for each tenant in their own database and reference them from the 'master' database with a view in the tenant schema. For example:

Tenant1DB
    dbo.Table1
    dbo.Table2

Tenant2DB
    dbo.Table1
    dbo.Table2

MasterDB
    tenant1.Table1 
    tenant1.Table2 
    tenant2.Table1 
    tenant2.Table2 

Where the objects mentioned above in the MasterDB database are views such as:

CREATE VIEW tenant1.Table1
AS
SELECT * FROM Tenant1DB.dbo.Table1

This way you can easily backup/restore individual tenant databases. Some other benefits of this strategy:

• Individual tenants can be restored without bringing the main database into single user mode.
• The system will scale out well as the tenant database can be moved to other servers.