Improve your protection for SQL Server backup

by Jun 4, 2022

Not protecting physical backups

You should think about the value of the data or the consequences of it getting into the wrong hands since a full database backup contains all data in your database. You will want to make sure you always protect this data when it is live and also in your backups if you are storing credit card data or social security numbers or passwords. Once someone has a backup, it is a very easy for them to do a restore to access the data.

Another reason you want to protect the files is to make sure they exist when you need to restore. Files could be deleted either on purpose or by accident and then no longer exist for the restore.

You should also limit access to where backups are stored and either limit or not allow backups from being copied to other locations.

Not encrypting backups

Another topic related to the physical protection is encrypting key data in the database in the event someone gains access to your backups.

By default, SQL Server does not encrypt data in the database.

However, you can encrypt key data within the databases by using encryption keys or transparent data encryption. You also can create encrypted backups with some third-party SQL Server backup tools. This will add another level of protection for your backups, just in case the backup falls into the wrong hands.

Storing backups on the same physical drives as database files

The default for creating backups is the same drive and path as the database files. So unless you make a conscious decision, your backups are perhaps stored right alongside your actual database files. You could lose both your live data and your backups if you lost the server or if the files on your drives were corrupt.

You can eliminate this risk by making sure you move your backups to other drives or to other servers or by creating the backups on different physical disks.

Writing multiple backups to the same physical file

Another issue is when multiple backups are written in the same physical file. This approach could cause a single point of failure if the file becomes corrupt. In addition, it is hard to tell what is in the backup file by just looking at the file name and or size. So if there are multiple backups in one file, the only way to determine what is in the file is to use the RESTORE commands.

A better way is to create one backup per file and use a consistent naming convention. This way you can just look at the file name and tell what kind of backup it is, for what database and when it was created. Also, if a file is corrupt, it will only affect the one backup in that file.

Allowing too many accounts to have access to create backups

Having your backups fall into the wrong hands could be a major issue. Another area to consider is whether several people can create their own database backups. They can take a copy of the entire database if someone does not have access to the location where the backups are stored and if they can create their own backups.

To avoid this from occurring, limit who has permission to create backups and monitor when backups are created, who is creating them, and where they are being created. You can do this by using the system tables and also the Windows event log and the SQL Server error log.

Not backing up system databases

Another thing that is often overlooked is backing up the system databases. It is unclear whether this is because of a lack of understanding of what is stored in these databases, or a case of not knowing how to use these for a restore.

The two main system databases that you should backup are master and msdb. The master database stores security information and metadata about the other databases. Msdb stores information about jobs, operators, and alerts. This is not very critical unless you are changing the model database.

There is no need to back up tempdb since SQL Server recreates tempdb each time you restart SQL Server. The other database that you could backup is the resource database. But this needs to be done outside SQL Server. In addition, you could script out important information for these databases as another way to recover important objects. But you will not have the historical data.

Read the whitepaper “Top SQL Server backup mistakes (and how to avoid them) by Greg Robidoux from MSSQLTips to learn more about improving SQL Server backups..

[Download PDF]