Sqlserver uses a password to encrypt the backup file to prevent unauthorized restoration of the database

Sqlserver uses a password to encrypt the backup file to prevent unauthorized restoration of the database

When backing up a database, you can specify a password for the media set, backup set, or both.

In the backup statement, the backup set password and media password are optional. Use a password to prevent unauthorized use of sqlserver to perform restoration operations and Add backup sets to media.

If a password is specified, you must also provide a media password to perform these operations.

For more information about media sets and backup sets, see msdn:Http://msdn.microsoft.com/zh-cn/library/ms186865 (V = SQL .90). aspx

 

Although the password helps prevent unauthorized access to media content using sqlserver, the password cannot prevent the destruction of media content. The password cannot completely prevent unauthorized access to media content,

The reason is that the data in the backup set is not encrypted and can be created for this purpose in theory.ProgramThe content in the backup file.

It is important to prevent unauthorized individuals from accessing the media when security is critical.

 

Note: from SQL Server 2012, the password and mediapassword options cannot be used to create a backup; however, you can still restore the backup created with the password !!!

 

The following is an explanation of the mediapassword and password options in the backup Statement on msdn.

 

1Password={Password| @ Password_variable}2 Set a password for the backup set. Password is a string. If a password is defined for the backup set, you must provide the password to perform any SQL Server restore operations on the backup set.3 However, the backup set password cannot prevent overwriting of the backup file. To prevent overwriting backup files, use the media set password.4This password provides low security. It is designed to prevent authorized or unauthorized users from using SQL Server2005The tool is incorrectly restored,5 However, the backup data cannot be read in other ways or by replacing the password. The best way to protect backups is to store backup tapes in secure locations,6Or back up the disk files protected by the appropriate access control list (ACL. The ACL should be set under the root directory of the backup.
 

 

 1 Mediapassword = {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 media set is created.  4   In addition, you must provide a media password when performing any restoration operations on the media set. Password-protected media can be overwritten only by reformatting.  5 This password provides low security. It is designed to prevent authorized or unauthorized users from using SQL Server 2005  The tool is incorrectly restored, 6   However, the backup data cannot be read in other ways or by replacing the password. The best way to protect backups is to store backup tapes in secure locations,  7 Or back up the disk files protected by the appropriate access control list (ACL. The ACL should be set under the root directory of the backup.

Backup creates a backup set using the backup set password provided by the password option.

In addition, backup typically verifies the media password provided by the mediapassword option before writing to the media. Backup does not verify the media password only when formatting the media,

This overwrites the media header. If backup writes a Media Header, backup assigns a media set password to the value specified in the mediapassword option.

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

Perform the experiment in three cases
(1) only mediapassword in the backup statement

(2) The backup statement only contains the password.

(3) The backup statement includes password and mediapassword.

 

 

 1   --  Scenario 1  2   Use  Master  3   Go  4   5   Backup   Database   [ Pratice  ]  6   To   Disk  =  '  D: \ pratice_fullbackup_201306110152.bak  '   With Mediapassword =  '  123456  '   7   Go  8  9   --  Restore  10   Restore   Database   [  Pratice  ]   From   Disk  =  '  D: \ pratice_fullbackup_201306110152.bak  '   With Mediapassword = '  123456  ' , Replace 

 1   --  Case 2  2   Use  Master  3   Go  4   5   Backup   Database   [ Hengshan  ]  6   To   Disk  =  '  D: \ hengshan_fullbackup_201306110152.bak  '   With Password =  '  123456  '   7   Go  8  9   --  Restore  10   Restore   Database   [  Hengshan  ]   From   Disk  =  '  D: \ hengshan_fullbackup_201306110152.bak  '   With Password = '  123456  ' , Replace 

 1   --  Case 3  2   Use  Master  3   Go  4   5   Backup   Database  Gposdb 6   To   Disk  =  '  D: \ gposdb_fullbackup_2013061110152.bak  '   With Mediapassword =  '  123456  ' , Password =  '  123456  '  7  Go  8   9   --  Restore  10   Restore   Database   [  Gposdb  ]   From   Disk  =  '  J: \ gposdb_fullbackup_2013076110152.bak  '  With Password =  '  123456  ' , Mediapassword =  '  123456  ' , Replace 

 

In test case 3, I moved the backup file and used a USB flash drive,But no error will be reported, as long as a password is provided.

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

But no error will be reported, as long as a password is provided.

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

The backup set information cannot be viewed using SSMs after encryption.

If not encrypted, you can see

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

Check which backup sets have a password, but only media_uuid. Do not know the backup set name ~

1 --Check which backup sets have a password added2 Use [MSDB]3 Go4 Select * From [DBO].[Backupmediaset]