All geek questions in one place

Best way to back up a specific schema?

I have a database with several tenants, each user has his own scheme.

What is the best way to back up one tenant (table layout)? As far as I know, SQL Server does not support backing up a single schema (full database only).

I need to back up the structure and data. In addition, it should be automated (ideally, I should also call it from SSMS).

I was thinking of exporting ddl and data as SQL statements. If there is a way to invoke the script creation and publishing wizard as a stored proc, I think this will work?

I'm on Sql Server 2008 R2 now, but I can upgrade.

+5
source share
1 answer

A few ideas.

Using file groups

Place the tables that each tenant has in their own file group. SQL Server has the ability to back up and restore individual groups of files . You can also perform some other operations, for example, if necessary, accept autonomous tenants if necessary. For instance:

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

Views and individual databases

This is probably not the right way, but it will work. Have tables for each tenant in your own database and link to them from the master database, taking into account the tenant's scheme. For instance:

 Tenant1DB dbo.Table1 dbo.Table2 Tenant2DB dbo.Table1 dbo.Table2 MasterDB tenant1.Table1 tenant1.Table2 tenant2.Table1 tenant2.Table2

If the objects mentioned above in the MasterDB database are such types as:

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

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

  • Individual tenants can be restored without transferring the main database to single-user mode.
  • The system will scale, as the tenant database can be transferred to other servers.
+3
source

Source: https://habr.com/ru/post/1204742/

More articles:


All Articles