Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Copy Data Into Multi-Tenancy Database For Customer Expand / Collapse
Posted Saturday, December 22, 2012 7:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:44 AM
Points: 6, Visits: 115
We've an application running a multi-tenanted database where each tenancy is for a specific customer.
(This can't be uncommon nowadays ?)

I'm trying to work out how to deal with restoring the data for an individual customer (TenancyId) if/when they request it.

We've daily backups of the full database so it's a matter of;
1. Deleting all the data from the Live database with the TenancyId
2. Restoring a backup as a temporary database,
3. Pulling the data from the backup db with the TenancyId
4. And inserting the data into the Live db with IDENTITY_INSERT ON against the tables

I'm wanting to hear if there's any easy way of automating steps 3 & 4 ?

There are currently 41 tables to deal with and I don't want to have to code individual inserts for each table.

The trouble with backup restores is that they're rarely used, difficult to test in the Live environment and can be a disaster if they don't work flawlessly.

So I'd like a procedure that doesn't rely on code being changed each time a database table is changed.

The nearest I've seen to this is the sp_generate_inserts code by Narayana Vyas Kondreddi (kudos++) which will output SQL Insert statements for each table.

Is it possible to direct this output to a .sql file ?

Suggestions greatly appreciated.

Kindest Regards,

Chris Clarke

Post #1399721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse