Best Practices for SQL Server Backup Safety

Best Practices for SQL Server Backup Safety

Has anyone ever dropped a database in production instead of development?

Have you ever run the wrong DELETE statement?

Let’s walk through these best practices and recommendations for SQL Server backups, so the crisis is easier to manage when disaster strikes.

Build a Proper Maintenance Plan

Use an appropriate recovery model, and understand your application’s business needs.

In the event of corruption or disaster, some databases may not be recoverable simply because backups don’t exist.

Recovery Time Objective (RTO) and Recovery Point Objective (RPO)

Think about how much data you can afford to lose in a failure (RPO), and how much downtime you can tolerate in a failure (RTO).

Together, these two terms make up the Service Level Agreement (SLA).

Based on these, we can now design our backups (full, differential, and transactional) and our disaster recovery plan.

Automate the Process

Good maintenance plans should pick up new databases automatically — if new ones are added or old ones are removed, the maintenance plans should keep running and adjust themselves.

Some databases not getting backed up is often a sign that backup procedures aren’t working correctly.

Avoid Concurrent Backups

Backups are often taken using third-party applications (which sometimes need to be replaced).

They can be taken using a snapshot method that can cause storage I/O freezes (leading to performance issues).

When this happens, everything pauses, waits for the freeze to complete, and then operations resume.

Keep Backup Files as Far Away as Possible

Make sure your database and backup (.bak) files aren’t on the same storage or physical drive.

If SQL Server, the operating system, or the hardware crashes, local drives may become unavailable.

Keep the files as far away as possible. Hurricanes, floods, and other kinds of disasters aren’t impossible.

For remote SQL Server machine backups, back up to the local disk first, then to a UNC path.

Measure the time difference.

This will help you determine whether the UNC path is a bottleneck.

Reduce the Load on Production

Another option for reducing the load on PROD is to back up to the local disk first.

Then have a secondary server pull that backup toward itself (be careful not to push it).

This eliminates the workload of copying backups off the PROD server.

Consider using Log Shipping or AlwaysOn to reduce some of these risks.

Verify and Test Your Backups

Add the CHECKSUM option to the WITH clause of your BACKUP command.

The backup process will verify that every data page written to the backup passes a checksum operation, to ensure consistency in the backup media.

Keep in mind that your data isn’t 100% safe just because the backup process completed successfully.

It’s important that they can be restored smoothly (make sure you have permissions on the path, etc.).

A common item on best backup practice lists is to frequently run restore tests on a test server, using the same options you’d use in real life.

Test Your Recovery Strategies

The ability to respond quickly to changes can determine your company’s success.

Back Up Your System Databases

A complete backup strategy includes a backup plan for your system databases — msdb, master, and model.

These databases matter because they contain system configuration as well as SQL Agent Job information.

Make a plan to back up your system databases at least as often as your FULL backups.

Protect Your Backups

Your backups hold the same data as your PROD databases.

Protect them the same way. Restrict access, use security controls, and review and audit who has access to your backup data.

If you’re running a newer version of SQL Server (starting with SQL Server 2014), consider using SQL Server’s database backup encryption feature.

If TDE (Transparent Data Encryption) is enabled on the database, choose different certificates or asymmetric keys for encrypting the database and the backup, to improve security.

Back Up Your Most Sensitive Data to Tape

Tape backups aren’t very common these days, but tape is far more resistant to physical shock than disk drives.

Tape media typically has a lifespan of around 20 to 30 years — about 3 times the lifespan of a typical hard drive — making it more reliable and less prone to wear.

Today’s tapes, like the LTO-8 (Linear Tape-Open Ultrium8) data cartridge, run at speeds of around 360 MB/s and can store around 12 TB of uncompressed data, which is more than enough for most business needs.

Magnetic tape is specifically designed for “cold data storage” (not frequently accessed data like backups).

In the event of a physical data center disaster, tapes are typically taken (or should be taken) off-site.