Did somebody drop a database in production instead of development?
Did you run the wrong DELETE statement?
Let’s follow these best practices and recommendations for SQL Server backups to make the crisis easier when the catastrophe strikes.
By the way, this check is a part of our SQL Server Health Check service.
Set up a proper maintenance plan
Use a proper recovery mode, understand the business needs of your application.
In the case of corruption or disaster, some databases may not be able to recover because of a lack of backups.
Recovery Time Objective (RTO) and recovery point objective (ROP)
Think about how much downtime you can afford to have in case of failure (RPO) and how much downtime you can afford to have in case of failure (RTO).
Both of those terms together add up to the Service Level Agreement (SLA).
Based on those, we can now design backups (fulls, differentials, and transactional) and the disaster recovery plan.
Automate the process
Good maintenance plans should automatically pick up DBS – if new ones get added or old ones removed, maintenance plans should just keep on going and self-adjust.
The fact that some DBS are not being backed up indicated that backup procedures might not be working correctly.
Avoid concurrent backups
The backups often are done using third-party apps (which sometimes needs to be modified).
They may be taken using a Snapshot method, which may cause the storage I/O to freeze up (causing performance issues).
When that happens, everything stops waits for freeze operation to complete, and then operations continue.
Keep backups files as far as possible
Make sure that your databases and backups files (bak files) are not on the same storage or physical drive.
If the SQL Server, the Operating System, or the hardware crashes, the local drives may not be available.
Keep the files, as far as possible. Hurricanes, floods, and other types of disasters are not impossible to happen.
For remote SQL Server computer backups, take backup to local disk, then to the UNC.
Measure the time difference.
This will help you to identify if the UNC can be a problem.
Reduce the load on production
For reducing the load on PROD, another option is to take backup to local disk first.
Then have a secondary server that pulls (notice not push) it over to it.
This eliminates the workload of copying backup from the PROD server.
Think about using Log Shipping or AlwaysOn to reduce some of the risks.
Verify and test your backups
Include the CHECKSUM condition in the WITH clause of your BACKUP command.
The backup operation will verify that each page of data written to the backup is passed through a checksum operation to ensure consistency on the backup media.
Keep in mind that your data is not 100% safe after the backup process has finished successfully.
It is essential that they can restore without issues (Make sure you have permissions to the path, etc.).
A common item in the list of backup best practices is to frequently do a restoring test using the options you are going to use in a real-life, on a test server.
Test your recovery strategies.
The ability to respond to changes quickly may determine the success of your company.
Backup the system databases
A complete backup strategy includes a backup plan for your system databases, msdb, master, and model.
These databases are essential because they contain system configuration and also the SQL Agent Jobs information.
Make a plan of backing up your system databases, at least as frequently as your FULL backups.
Protect your backups
Your backups have all the same data as your PROD databases.
Protect them in the same way. Restricting access, using security measures, and reviewing and controlling who has access to your backup data.
If you are using a recent version of SQL Server (starting in SQL Server 2014), consider using the SQL Server database backup encryption feature.
If the database is TDE (transparent data encryption) enabled, choose different certificates or asymmetric keys for encrypting the database and the backup to increase security.
Backup your most sensitive data to tape
Tape backups are not very common nowadays, but the tape is much more resistant to physical shock than disk drives.
They are more reliable, less sensitive to wear, as general media life for tapes are around 20 to 30 years, 3x the lifespan of a regular hard drive.
Today’s tapes, LTO-8 (Linear Tape-Open Ultrium8) data cartridge, have high speed at around 360 MB/sec and can store around 12 TB of uncompressed data, which is still pretty much sufficient for most of the business needs.
Magnetic tapes are specifically designed for “cold data storage” (not frequently accessed data like backups).
Tapes are often taken offsite (or should be) in case of a physical data center disaster.
More information
Microsoft – Back Up and Restore of SQL Server Databases
Microsoft – Backup Encryption
Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.
