SQL Server Tips

Keep SQL Server Backups Safe: Best Practices

Updated
6 min read
Written by
Mark Varnas

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.

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. Whether new ones get added or old ones get removed, maintenance plans should self-adjust and keep on going.

The fact that some DBs are not being backed up indicates 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 do sometimes happen.

For remote SQL Server computer backups, take backup to the local disk, then to the UNC.

Then measure the time difference.

This will help you to identify if the UNC will be a problem.

Reduce the load on production

To reduce 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 to back 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 from 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. In general, the 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 12TB 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

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Leave a Comment

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials