I’ve been working with SQL Server for a long time, and one of the things I’ve learned is to not assume others view the platform and its administration needs in the same way that I do. I have usually started examining new instances with the same skepticism I’d use if my Mom told me she’d installed the software. I’m sure she could do it, and likely use some wizard and Google to get some backup scheme implemented, but I don’t know that it would be the schema I’d want to use.
This week I noticed a piece from Lori Brown, of SQLRx, which talked about a few basic settings that I’d always want running on my systems. One of these is the CHECKSUM setting. It’s a checkbox in the SSMS dialog, and an option in T-SQL. Most third party tools, like SQL Backup Pro, include similar settings. To me, this ought not to be a setting, but rather a default that always runs. NO_CHECKSUM is the default, which is silly in 2019.
In any case, I’ve seen more than a few presentations on the backup process in SQL Server. They always seem to be beginner sessions, always have more people than I expect, and remind me that this process, which is solid and stable, still has a lot that people don’t think about. There are certainly nuances to performing backups, and restores, in a manner that doesn’t generate any RGEs.
I don’t usually use the VERIFYONLY option, as to me the file isn’t really tested until it’s restore. This is one reason I recommend having a process to regularly restore your backup files on a test system. Not for use, though you can certainly use them, but more just to ensure your file system, your storage network, all the hardware involved hasn’t caused any issues with the backup file. If you build a server for this process, make sure you add enough RAM, as someone recently learned.
My feeling is that backup and restore is the most critical aspect of managing your SQL Server instances. This is the first thing I get working, and the number one ongoing concern I have to ensuring data is available. Second would be security, and everything else follows from there, but having a solid backup and restore process is the foundation of all other system administration.
There are lots of ways you can learn more. We have articles, a free ebook, and more at SQLServerCentral. The best way, however, is what Lori has done. Do some testing. Run through some scenarios, check how long things take in your environment, and ensure that your backups are capable of meeting the RTO and RPO needs of your organization.