February 4, 2022 at 2:14 pm
I'm the default IT person for my wife's rural medical office (because I could identify the computer in a pile of electronics).
We're running Windows Server 2016 Standard with SQL Server 2016 on a Supermicro X11SRA motherboard.
Our server crashed two days ago (motherboard died, I think) and due to my poor backup policy, I've lost the transactions for the day of the crash. I've moved SQL to a backup server and restored the database to my latest backup. When I manage to get my server back to life, probably in a week, I expect to find the missing data on it, but by then, the database will have marched on (it already has). I can't get to the data on the original disks until the unit is fixed, as it is on M.2 drives, which I don't have another way to access.
Is there any way to re-merge the data? I'm guessing no.
February 4, 2022 at 2:22 pm
I don't think I'll be telling you anything that you don't already know ...
If the 'missing' data exists only on the crashed server, you have to wait until the crashed server is back online, unless you can somehow get the relevant disk out of the server and bring it online in another machine.
Once you have access to the missing data, identifying it and then pushing it back into the restored database should be possible – the complexity of doing that depends heavily on the structure of your database.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 4, 2022 at 2:53 pm
I'm not a wizard at anything around SQL, so since I can't get to the data before the disparity grows, I think I'm faced with losing the one day or losing everything forward until the new server is repaired. I'm in a very rural area and finding another device than can read a Raid1 M.2 device pair is unlikely.
February 4, 2022 at 3:07 pm
Recovering the missing day's data might not be as difficult as you think. People here will help you.
Approximately how many tables in the database, in total, are likely to contain the missing data?
Do these tables contain DateCreated (or equivalent) columns?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 4, 2022 at 3:21 pm
and do all tables contain identity columns?
if so you could look at restoring your db, take note of the values for all tables, set the identity to a higher value that would not have been reachable on the old system between backup time and failure time. this would enable you to identify new records easily and also (potentially) allow you easily add them to the new "version" of the tables.
updates could be a bit more tricky but without full schema and business rules we can only guess the amount of work involved.
there are also ways of knowing all that is done on the "new" system - (CDC could be enabled, and/or triggers added to every single table to record all data changes) - but all require fast action and always a bit of work afterwards to get things right.
February 4, 2022 at 6:23 pm
This is a EHR software package. It has 1360 tables and I assume everything has date tags, but access to folks that actually understand the structure is not a customer thing.
Upon reflection, I think the users will have reconstructed most of their lost work by the time I get my hands on the lost data, so I'll just offer myself up for a tar and feathering.
February 4, 2022 at 8:00 pm
This is a EHR software package. It has 1360 tables and I assume everything has date tags, but access to folks that actually understand the structure is not a customer thing.
Upon reflection, I think the users will have reconstructed most of their lost work by the time I get my hands on the lost data, so I'll just offer myself up for a tar and feathering.
Just make sure your new system has full backups and frequent transaction log backups - that are either backed up to a share or copied to that share, and moved to offline storage for long-term retention. Or - you could implement backups to Azure Storage.
When SQL Server 2022 is released - plan to migrate to that system and work with the vendor to get up on that version. Once you have that it will be much easier to setup and configure a DR solution in Azure - but if that isn't possible you could still implement an AG solution to Azure, it is just a bit more work.
Depending on the EHR system - you may want to consider their cloud offerings. Some of the EHR solutions have very good cloud-based solutions that are reasonably priced.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 4, 2022 at 8:10 pm
The practice is closing on May 1 due to my wife's retirement. The EHR and SQL will have to hang around for collections and patient records, but I doubt we'll be upgrading anything.
February 5, 2022 at 3:52 pm
The practice is closing on May 1 due to my wife's retirement. The EHR and SQL will have to hang around for collections and patient records, but I doubt we'll be upgrading anything.
Understood - no reason to upgrade, but making sure you have a good backup strategy is still well worth considering. As long as you can get the backup files off that server onto some other storage you will make it much easier to recover if something were to happen again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 5, 2022 at 4:23 pm
I upped my differential freq to hourly, and all backups are stored on an external USB drive. I need to get my off-site transfers running again, but my script was on the dead server so I have to re-invent the wheel...
February 5, 2022 at 4:35 pm
There is a difference between transaction log backups and differential backups. If your databases are in full recovery model - you must take transaction log backups and I recommend those be done at 15 minute intervals (or more frequently).
If all you are taking are differential backups then you are at risk of losing up to 1 hour of data - because those backups cannot be used to restore to a point in time.
If you really meant transaction log backups, then I would recommend running those every 15 minutes. This reduces the risk of data loss - as long as you have an unbroken chain of log backups you can restore up to the latest available log backup, and if you are in a position to take a tail-log backup you could recover with no data loss.
But if truly differentials - I would recommend reviewing that strategy and understanding the risks of data loss.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 5, 2022 at 4:45 pm
I did mean differential. I'm a retired hardware engineer with no experience with Server or SQL except for my wife's office, and my brain hurts if I try to understand those differences. We'll live with risking an hour. We've only had one problem in 22 years, and that was just the other day.
February 7, 2022 at 3:37 pm
Edit: Duplicate post
February 7, 2022 at 3:38 pm
Setting up transaction log backup is just a few different clicks. Jeffrey Williams' advice is good.
You can use a Maintenance Plan wizard and it's quite easy.
Make sure the database option is set to FULL recovery.
Add Trans Log backups
Add a step to delete the logs after 'X' days. 30 - 45 days is common. While having FULL and DIFF that go back further.
Your DIFFs can be less frequent once you implements TRANS log backups.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply