SQL Server encrypts the backup file with a password to prevent unauthorized restore of the database

Original: SQL Server encrypts the backup file with a password to prevent unauthorized restore of the database

SQL Server encrypts the backup file with a password to prevent unauthorized restore of the database

When backing up a database, users can specify a password for a media set, a backup set, or both

In the BACKUP statement, define the backup set password and the media password as optional features. Using a password prevents unauthorized restore operations and the addition of backup sets to the media using SQL Server Tools.

If a password is specified, the user must also provide a media password to perform these operations

For media sets and backup sets you can refer to MSDN:http://msdn.microsoft.com/zh-cn/library/ms186865 (v=sql.90). aspx

Although using passwords is helpful in preventing unauthorized access to media content with SQL Server Tools, passwords do not prevent media content from being compromised. Passwords do not completely prevent unauthorized access to media content.

The reason is that the data in the backup set is not encrypted and can theoretically be viewed in the backup file by a program created specifically for this purpose.

It is important to prevent unauthorized individuals from accessing the media for security-critical situations

Note: Starting with SQL Server 2012, the PASSWORD and Mediapassword options are no longer available for creating backups, but you can still restore a backup created with a password!!!

The following is an explanation of MSDN's Mediapassword options and password options in the BACKUP statement

1 = | @password_variable }23 However, the backup set password does not prevent overwriting the backup file. To prevent overwriting the backup file, use the media set password 42005  tool for incorrect restore,5  However, you cannot prevent the backup data from being read by other means or by replacing the password. A best practice for securing backups is to store the backup tapes in a secure location,6 or back up to a disk file that is protected by an appropriate access control list (ACL). The ACL should be set in the root directory where the backup was created.

1Mediapassword={Mediapassword| @mediapassword_variable }2 set a password for the media set. Mediapassword is a string. 3 If a password is defined for a media set, the password must be provided before the backup set is created on the media set. 4 Also, you must provide a media password when performing any restore operation from the media set. Password-protected media can only be overwritten by reformatting5This password provides a low level of security. It is designed to prevent authorized users or unauthorized users from using SQL Server2005tool to perform an incorrect restore,6 However, you cannot prevent the backup data from being read by other means or by replacing the password. A best practice for securing backups is to store the backup tapes in a secure location,7Or back up to a disk file that is protected by an appropriate access control list (ACL). The ACL should be set in the root directory where the backup was created.

Backup uses the backup set password provided by the PASSWORD option to create a back-up set.

In addition, BACKUP typically verifies the media password provided by the Mediapassword option before writing to the media. The only time that BACKUP does not verify the media password is when the media is formatted

This overrides the media header. If backup writes to the media header, backup assigns the media set password to the value specified in the Mediapassword option.

------------------------------------- Gorgeous split-line ----------------------------------------------------------- -

Experiment in three different situations
(1) Only Mediapassword in the BACKUP statement

(2) Only password in the BACKUP statement

(3) The BACKUP statement has password and Mediapassword

1 --situation one2  UseMaster3 GO4 5 Backup DATABASE [Pratice]6  to Disk='D:\pratice_fullbackup_201306110152.bak'  withMediapassword='123456' 7 GO8 9 --RestoreTen RESTORE DATABASE [Pratice]  from DISK='D:\pratice_fullbackup_201306110152.bak'  withMediapassword='123456',REPLACE

1 --Scenario Two2  UseMaster3 GO4 5 Backup DATABASE [Hengshan]6  to Disk='D:\hengshan_fullbackup_201306110152.bak'  withPassword='123456' 7 GO8 9 --RestoreTen RESTORE DATABASE [Hengshan]  from DISK='D:\hengshan_fullbackup_201306110152.bak'  withPassword='123456',REPLACE

1 --situation three2  UseMaster3 GO4 5 Backup DATABASEGposdb6  to Disk='D:\GPOSDB_fullbackup_2013061110152.bak'  withMediapassword='123456', PASSWORD='123456'7 GO8 9 --RestoreTen RESTORE DATABASE [Gposdb]  from DISK='J:\GPOSDB_fullbackup_2013076110152.bak'  withPASSWORD='123456', Mediapassword='123456',REPLACE

In the test situation three I moved the backup file, and use a USB flash drive, but will not error, as long as the provision of a password can be

Because the media set and backup set is not very familiar with, so I backed up the backup file on the D drive, and then moved the backup file to the local hard disk on the F drive and USB drive, the drive letter is J

But no error, as long as there is a password to provide it

----------------------------------------------- Gorgeous split-line ------------------------------------------------- --------

After encrypting, use SSMS to not see the backup set information

If not encrypted, you can see it.

------------------------------------------------- Gorgeous split-line ----------------------------------------------- -------------------

finally see which backup sets add a password, but only media_uuid, do not know the name of the backup set, depressed ~

1 -- See which backup sets have passwords added 2  Use [msdb] 3 GO 4 SELECT *  from [dbo]. [backupmediaset]

SQL Server encrypts the backup file with a password to prevent unauthorized restore of the database