March 30, 2023 at 5:47 pm
Hi, is it possible to restore an SQL Server DB to an earlier snapshot while preserving the work performed after the snapshot? I was thinking about backing up the transaction log, reverting to the earlier snapshot, then restoring the backed up transaction log. However, I can imagine problems if there are any format changes between versions.
Backgound: We create workflow webapps built on a low-code SaaS plaform installed and managed an on-prem server. Periodically, we need to update to the latest SaaS version...which also affects the database. We recently encountered a scenario where we successfully completed regression testing on the latest version and promoted it into production. A few hours into the work day, a significant problem was discovered with non-administrator accounts...something we hadn't tested. We had to restore to our pre-update backup. The problem was...many users lost about four (4) hours of work (each). They were furious!
Thanks!
March 30, 2023 at 7:10 pm
Any chance of restoring to another server and copying the data?
March 30, 2023 at 7:52 pm
Options for this would be
1 - fix forward
2 - restore the pre-update DB side by side or on another server and use things like RedGate schema compare to revert the object changes and RedGate data compare to compare the data and rollback things which need rolling back. There are other tools for this also, just in personally love the RedGate tools for this.
3 - accept the risk that rollbacks will result in the loss of work
4 - control all database changes via scripts so you can easily create a rollback script at the same time.
And most importantly get the database into source control. If the DB schema is in source code you can easily compare schema changes to roll back.
March 31, 2023 at 4:07 pm
Thank you for your response! Not being a DBA, I need to do some research to understand some of your comments. I was hoping there was an easy SSMS process to restore user work after a rollback.
March 31, 2023 at 4:17 pm
Thanks for the response. Not being a DBA, I need to figure out how to do that. Let's say I have a replica server and stop replication prior to the version update. I update the primary server, put it online, then let users work on it for a few hours. How, at that point do I copy the "user-created changes" (but not the update) to the replica server...as the rollback process?
Note: Nearly every update has some amount of format changes that need to be considered.
April 3, 2023 at 1:14 pm
When you say 4 hours of work, do you mean database schema changes, or data that has been added as part of normal operations?
If this is data and not schema changes and I'm understanding your development workflows:
The work should be manually redone. Either by you or some developer who can extract the new work and modify it to fit the new design you have to implement (or the old design before your changes). Or the users have to redo the work. Presumably a developer or analyst or anyone really who's good with data and has tools to massage, shape, and verify data will be able to do this quicker than the original users; the users would also appreciate that you're cleaning up your own mess, as well. You should still get users to validate the restore. This may seem unattractive, that's because it should be. Work on making your tests more complete and thorough. Get your business users to provide more thourough test scenarios, clearly it's important.
If these changes are schema changes (new columns, tables, scripts, etc) that some developers are performing:
Make the process of performing the change a script for each change, and this script is part of your change management workflow. It is the artifact that is passed from developer, to tester, to approver, to implementer (even if that's all the same person). If things go sideways you can reapply any work that is not known to break things by running and validating the same way you would at the implementation step. This is an approach that you should strongly consider even if it's not the scearnio you're painting.
These are process issues rather than database admin issues. Try to find some help on database DevOps, modern change management workflows, testing frameworks or processes. I may be misunderstanding, but it sounds like your development workflow could use a lot of help. 90% of the problems I'm describing are fixed with process rather than software. Some of the solutions are tedious and slow to perform manually, which is why there are so many products begging for corporate $$$. Start with solving workflows first, then see what you can build yourself reasonably, then what you can buy.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply