Backup/Recover of row data in a multi-Company database
-
We have a web application using SQL Server 2008 R2 that contains data for tracking time that another company has expressed interest in using, we initially set up the database such that all end-user tables have a foreign key to what company that row belongs to. Is there a good way to backup/restore the data for a specific company across the tables, such that if the new company says tomorrow that they want their data restored to yesterday; it can be restored without impacting all the other companies data as well? I'm trying to avoid writing some form of stored procedure that selects and then have to do an insert/update/delete to put the data back. Thanks for any insight.
Dennis Daugherty
-
We have a web application using SQL Server 2008 R2 that contains data for tracking time that another company has expressed interest in using, we initially set up the database such that all end-user tables have a foreign key to what company that row belongs to. Is there a good way to backup/restore the data for a specific company across the tables, such that if the new company says tomorrow that they want their data restored to yesterday; it can be restored without impacting all the other companies data as well? I'm trying to avoid writing some form of stored procedure that selects and then have to do an insert/update/delete to put the data back. Thanks for any insight.
Dennis Daugherty
-
Could you simply use bcp[^] to 'export' or 'import' the data. For your case, when exporting, you should define a custom query for the bcp to run.
The need to optimize rises from a bad design.My articles[^]
-
I guess I could if I turned identity insert on and then just delete all for a company and then import ... I'll think on that one ...
Or if you have foreign keys between several tables, you could set the identity insert off to ensure that the keys remain the same as they originally were. Another mechanism you could consider is SSIS where you could break the import/export to proper blocks, define a workflow and if necessary add logic.
The need to optimize rises from a bad design.My articles[^]
-
We have a web application using SQL Server 2008 R2 that contains data for tracking time that another company has expressed interest in using, we initially set up the database such that all end-user tables have a foreign key to what company that row belongs to. Is there a good way to backup/restore the data for a specific company across the tables, such that if the new company says tomorrow that they want their data restored to yesterday; it can be restored without impacting all the other companies data as well? I'm trying to avoid writing some form of stored procedure that selects and then have to do an insert/update/delete to put the data back. Thanks for any insight.
Dennis Daugherty
Is it possible to supply a separate environment for their needs. Or are they really trying to reset their production environment back to a prior date. We often have a need for a department to want a restore of a previous date for reporting/investigation reasons, this does not need to impact on the production environment as they get another database environment created specifically for their needs.
Never underestimate the power of human stupidity RAH
-
Is it possible to supply a separate environment for their needs. Or are they really trying to reset their production environment back to a prior date. We often have a need for a department to want a restore of a previous date for reporting/investigation reasons, this does not need to impact on the production environment as they get another database environment created specifically for their needs.
Never underestimate the power of human stupidity RAH
This is more for a restore after they do something dumb ... like "I just deleted all the invoices for customer A when I meant B; can you get them back?" My answer would be I can put your data back to 3am last night, you'll loose everything you did today but your invoices will be back.
-
This is more for a restore after they do something dumb ... like "I just deleted all the invoices for customer A when I meant B; can you get them back?" My answer would be I can put your data back to 3am last night, you'll loose everything you did today but your invoices will be back.
Ugh, nasty, especially when you mix other clients into the data, I would suggest this could only be fixed by a custom script, charge them a fortune as a lesson and hope like hell you can deliver. I would then implement a logging/audit strategy on the database or separate each client to their own environment. If you screw up another clients data b/c of this issue your whole business case just went out the window!
Never underestimate the power of human stupidity RAH