• Article

Security Considerations for Backup and Restore (SQL Server)

This topic discusses recommendations for keeping your backups of Microsoft SQL Server databases safe.

Note

For information about file permissions that are set whenever a database is backed up or restored, see Securing Data and Log Files.

Managing Log Backups

If you are using the full recovery model or bulk-logged recovery model, and you lose a log backup, you might not be able to restore the database past the preceding backup. Consider making multiple copies of log backups by backing up the log to disk and then copying the disk file to another device, such as a separate disk or tape.

We recommend that you store the chain of log backups for a series of database backups. If your most recent full database backup is unusable, you can restore an earlier full database backup and then restore all the transaction log backups that were created since that earlier full database backup.

If you lose a log backup, we recommend that you retain transaction logs backups that precede the missing log backup, in case you ever want to restore the database to a point in time within those backups.

Physical Protection

To help protect your backup tapes, we recommend that store them in a secure, offsite location.

To help protect your backup disk files, we recommend that you back up only to disk files that are protected by restrictive access control lists (ACLs). The ACLs should be set on the directory root under which the backups are created. In some cases, you might want to further protect disk-based backups by using NTFS encrypting file system (EFS). Additionally, we recommend that you use Windows backup to back up your SQL Server disk backups onto tapes that you then store in a secure, off-site location. For more information, see the Windows documentation.

Backup Password Protection

SQL Server supports password protection for backup media and backup sets.

Important

The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users. It does not prevent the reading of the backup data by another method or the replacement of the password. This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Passwords are not required to perform backup operations, but they provide an added level of security. You can use them in addition to using SQL Server security roles. The use of password protection helps guard against unauthorized or unintentional actions such as:

  • Restoration of databases

  • Appends to the media

  • Overwriting of the media

Important

Password security does not prevent overwriting the media by formatting it or by using it for a continuation tape. Additionally, specifying a password does not encrypt the data in any way.

Media set password

This provides weak protection for the data saved to a media set. The media set password is saved when the media header is written; it cannot be changed. If a password was provided when the media set was formatted, that password must be supplied to create a backup set on that media set. Additionally, that media password must also be supplied to perform any restore operation from the media set.

Note

You can only use the media for SQL Server backup and restore operations.

To specify a media set password, use the MEDIAPASSWORD option in the BACKUP or RESTORE statement.

Backup set password

This provides weak protection for a particular backup set. A different backup set password can be used for each backup set on the media. A backup set password is created when the backup set is written to the media. If a password is defined for the backup set, the password must be supplied to perform any restore of that backup set.

To specify a backup set password, use the PASSWORD option in your BACKUP or RESTORE statement.

Only Restore Backups from Trusted Sources

We recommend that you do not attach or restore databases from unknown or untrusted sources. These databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server. Also, examine the code, such as stored procedures or other user-defined code, in the database.