create encrypted database backup

[aman]

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."

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

Any help will be much appreciated.

 

[isladogs]

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]

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

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

 

[CJ_London]

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]

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:

  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]

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]

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