create encrypted database backup (1 Viewer)

aman · May 25, 2018

Hi Guys

I am using the following script to create encrypted database backups in SQL Server but I am getting an error message

"Msg 3032, Level 16, State 2, Line 1
One or more of the options (mediapassword) are not supported for this statement. Review the documentation for supported options."

Code:

BACKUP DATABASE AdventureWorks
TO DISK='C:\AdventureWorks.BAK'
WITH MEDIAPASSWORD='C0mplexP@ssW0rd'
GO

Any help will be much appreciated.

isladogs · May 25, 2018

Just a guess but the issue may be nothing to do with encryption.
Try changing the destination path so it's not pointing to the root C drive

aman · May 25, 2018

This is m exact code but it gives me the same error message :

Code:

BACKUP DATABASE Test_Aman
TO DISK='\\Corpfiler1\Data\Access Databases\Group Manufacturing\Test_Aman.BAK'
WITH MEDIAPASSWORD='C0mplexP@ssW0rd'
GO

CJ_London · May 25, 2018

take a look at this link - may be you don't have the right version of sql server?

https://docs.microsoft.com/en-us/sq...estore/backup-encryption?view=sql-server-2017

isladogs · May 25, 2018

It would have helped if you had posted that originally as the original was misleading....

I've not used MEDIAPASSWORD in my backups and according to this link, it is no longer supported:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2737fefd-1060-4424-8174-a1c5bd075b5a/sql-server-2014-set-password-for-backup-file?forum=sqlkjmanageability

FWIW, I use connection strings including SQL passwords with passthrough queries for the purpose. Example of my code:

Code:

  qdfPassThrough.SQL = "BACKUP DATABASE [" & GetSchConstantsValue("strSQLDatabase") & "]" & _
                                " TO DISK = N'" & CurrentDBDir & "SDABE_FullDBBackup_" & _
                                    strBackupDateTime & ".BAK'" & _
                                " WITH NOFORMAT," & _
                                " NOINIT," & _
                                " NAME = N'" & GetSchConstantsValue("strSQLDatabase") & "-Full Database Backup'," & _
                                " SKIP," & _
                                " NOREWIND," & _
                                " NOUNLOAD," & _
                                " STATS = 1;"
                                
                strBackupName = "SDABE_FullDBBackup_"

See if this link helps you find a solution:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-2017

Minty · May 25, 2018

Depending on youtr SQL version From Microsoft ;
Beginning with SQL Server 2012 (11.x), the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. It is still possible to restore backups created with passwords.

Even if that doesn't apply I don't think you can specify a mediapassword on a non media set - you would have to set the destination as a media set.

Have a good read here https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

Edit : Dam Ridders faster fingers....

Albert Dicosta · Sep 7, 2018

Try this way:
BACKUP LOG [db name] WITH NO_LOG
DBCC SHRINKFILE ([log file logical name], [new size in Mb])

create encrypted database backup (1 Viewer)

aman

Registered User.

isladogs

MVP / VIP

aman

Registered User.

CJ_London

Super Moderator

isladogs

MVP / VIP

Minty

AWF VIP

Albert Dicosta

Registered User.

Similar threads

Users who are viewing this thread