We require an application to allow us to backup and restore portions of our multi-tenanted database.
The database is an ecommerce system used by multiple websites, each with their own set of records. At the topmost level is a Website with a WebsiteID, below this are other tables, for instance Products, Categories, Orders etc (over 50 tables).
We need to be able to define a single Website to be backed up, possibly to a staging database. All related tables must also be included (Orders, Products, Stock etc. etc.). The relationships should be inferred through foreign key relationships, so that if new tables are added these are automatically included in the backup. Likewise, if new columns are added these should also be included in the backup file without the need to change the source code.
When restoring the data back it is assumed that the schema of the table would not have changed since the backup was made – if it has then the backup will not be restored (we will invalidate them).
If the schema has not changed then it should be possible to restore the backup from the staging database, including all of the relationships. The destination Website would be deleted (if it already exists) and replaced with the backup, there is no need to merge the data into an existing Website. It is vital that the relationships are maintained, so if in the backup and Order with and OrderID of 1234 is made, and it has two products with IDs of 345 and 678 then when restored the Order should still have the same two related products, although the IDs will have changed. This functionality will allow us to merge blocks of related data from several databases into another single database, so IDENTITY_INSERT cannot be used.
My experience with SQL Server and the ETL tools available plus my skills with .Net (C# and VB.Net) give me many options to meet your needs. I look forward to my project paid with non U.S currency!
22 freelancers are bidding on average £200 for this job
As a standard practice in our disaster management and data loss mitigation procedure, we build in codes in our software to periodically - Please see PMB for details.