Home Forums It's time to get real f&*(g serious about backups

Not sure I entirely agree with that statement, Bert.

JamesNT

You should. It's true.

The part about the files being generated is true. However, any transactions not committed to the database will be lost. This leads to the possibility of the database being in an inconsistent state and SQL Server refusing to mount it or marking the database as corrupt or "in recovery" until administrative action is taken.

JamesNT

OK, I need to take a page from Indy's book and be careful with the multi-tasking.

I almost posted all the steps needed to lock a Windows 2016 domain down with Software Restriction Policies in this thread. Not only wrong thread but also entirely wrong forum.

JamesNT

Two posts deleted due to being irrelevant to the thread. Being handled in PM.

Bert, Thanks for the feedback. CrashPlan does allow for versioning of backups.

And you've convinced me to reconsider disc images. What software do you recommend to do this as an automated task? (I'll save to different computer on my network, and then physically copy those to flash drives since they'll be too large to save to CrashPlan over my slow DSL connection).

Good. Glad to help. I like Macrium for clients. I know BackupAssist is only server compatible.

https://www.macrium.com/reflect-workstation

You can get trial versions of each. The big advantage to Server and Server Plus is they have the ability to back up SQL continuously. I believe one of them you can add on later.

Windows Server Backup isn't too bad either. It's a big reason we recommend Hyper-V over VMWare. It has a native backup solution. I like Veeam and Altaro as much as the next guy, but free is good too.

Yes, but this is Win 7.

Heartily agree. A small practice with a low transaction rate can *probably* get away with that, but outfits with 10-1000 transactions per second will always have a large trailing "commit queue"

We always stop and grab both.Because data loss, and minimize the O-Sh!t effect.

However, in our case it is already lost due to SQL Server being turned off. hence there is no way those transactions can get saved into AC database. I am sure you will disagree.

It isn't the best way to do a backup anyway.

If you are looking for commercial, I like Acronis as they have a proven solution for dis-similar hardware, or even P->V (Physical to Virtual) restores.

There are open-source solutions as well, but none are what I would call end-user friendly.

I'll let James answer as to the SQL 2012 internals, but my understanding is that in stopping SQL, memory is emptied by either committing forward or writing out pending transactions so that the end-state is ACID compliant, known to the parent database, and can be successfully mounted and started. Nothing known is lost.

"Best" depends on the skills, access, knowledge, and aptitude. Not everyone has what is required to do SQL .BAKs, or to schedule full and differentials.

As Dirty Harry says, "A man's got to know his limitations."

Your limitations or mine? I am fully confident with mine.

Windows 7 also has the same backup engine. Control Panel>Backup and Restore

Essentials uses the same engine on windows client machines to maintain its backup set of client computers.

It's also on Windows 8 and Windows 10. It's pretty barebones with no email notifications or anything. Even the restore is barebones. It's still way better than just a file backup. Backup assist actually uses it in their software too. Makes the same calls to the built-in VSS writers in Windows. Solutions like Acronis developed their own VSS writers.

How long does it take for information to get written into the SQL database for Amazing Charts? I don't stop the service and copy the *.mdf and *.ldf files until I have been logged out of AC for at least five minutes (have to get the thumb drives and access the server physically), and all my staff have been gone for more than 30 minutes. I would think that delayed writes to the database (on a system with only 6 users) have been made and I shouldn't have to worry about data loss.

Fraction of a second. I would still see if Macrium has a desktop product that will back up SQL. Maybe you can run Macrium Server on Windows 7 Pro. Most of the higher end versions of backup software will back up SQL continuously or near continuously. Every one to five minutes. In seconds. And, it takes up way less space.

For instance, I have five days of AC SQL backups every 15 minutes which ends up being 48 backups a day (only do 7 am to 7 pm) = 240 complete backups of SQL's .mdf and .ldf databases. That is 5GBs. Where just the actual databases are more than that. And, it's automatic. Constantly backing up daily and being backed up the right way. (Not that your way isn't).

And, if you're worried about what the others have said about data losses, etc., back up the .ldf files. No big deal.