I’ve written many blog post and articles over the years about SQL Server backups and restore options. I am constantly amazed at how often I find databases that are not being properly backed up and are critical to the organizations that utilize them.
Fortunately, as organizations move to cloud solutions, things are getting slightly better. For those utilizing Azure SQL Database and Azure SQL Managed Instance, backups are automated. SQL Server on Azure VMs is not as simple. There are managed backup options, however those have to be opted into, otherwise it is the customers responsibility to manage their own backups.
I often find organizations that are relying on VM snapshots to be their back up protection. While that might allow an organization to recover the OS and SQL Server as of the time of the backup, that will likely not be adequate if and when a restore is needed. For any databases that have regular updates, being able to restore to a point as close to the time of disruption is key to help minimize data loss.
For those databases that we need to minimize any data loss, we need regular backups which would include transaction log backups.
Organizations should be focused on “Recovery Time Objective” and Recovery Point Objective” RTO and RPO. RTO is how quickly they need to recovery an environment, while RPO is how much data they can afford to lose. Typically 5 to 15 minutes is the agreed upon RPO while RTO I’ve seen as little as 1 hour and as much as several days. It really all depends on the system. The shorter the RTO, the more organizations need to consider High Availability solutions. The lower the RPO, the quicker the log backups should be and replicating backups offsite.
Several years ago I blogged about a script I use often to check the status of backups on a server. The script in the blog post returns the database name, recovery model, last full, last differential, and the last two transaction log backups. With these results, I can determine if any databases are in full recovery with no recent transaction log backups (this is bad due to the transaction log continuing to grow due to not being truncated), databases in SIMPLE recovery that should be confirmed that point-in-time recovery is not needed, and with the last two transaction log backups I can determine the backup interval. https://www.timradney.com/how-to-check-for-last-sql-server-backup/
There are numerous third party tools that organizations can use to take backups, you can use database maintenance plans, you can create your own routine, or use Ola Hallengren’s maintenance solution. Regardless of the solution you put in place to take back ups, you need to regularly test them to 1) ensure your backups are valid, 2) know the process to restore, 3) time the process to make sure it meets your service level agreements.
The post SQL Server Backups appeared first on Tim Radney.