Copy Data Into Multi-Tenancy Database For Customer

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by bapat_amit » Copy Data Into Multi-Tenancy Database For...

Copy Data Into Multi-Tenancy Database For Customer

Rate Topic

Display Mode

Topic Options

Author

Message

Chris Clarke-296861

Posted Saturday, December 22, 2012 7:57 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:41 PM
Points: 6, Visits: 104

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 »

Permissions