December 22, 2012 at 7:57 pm
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply