I have a task where i have to take the backup with encryption and restore it to destination using the powershell.

I used the invoke-sql command and use the tsql in it but this command is not reliable for us rather we want to use the SQL powershell i.e backup-sqldatabase & restore sqldatabase.

my questions are what are the process to perform it (please i dont want to use invoke-sqlcmd)

  • create master using powershell ( i use "create master key encryption by password = 'Abcd1234!'" in invoke-sqlcommand)
  • Create certificate using powershell
  • backup-certificate
  • create backup using encryption

You can use dbatools which is powershell based.

For your case you need to have a 2 step approach.

  1. use the cmdlets in Backup and Restore – Certificates




then use Copy-DbaDatabase with -BackupRestore switch. Alternately, you can use Backup-DbaDatabase and then Restore-DbaDatabase as per your needs.

  • i dont think, i can install that module due to limitation at work..but sqlps & sqlserver powershell modules are available – Waqas Sarwar MVP Nov 21 '18 at 15:41
  • 2
    Then the only option that you have is to code something that is already out by your self – Kin Shah Nov 21 '18 at 15:45
  • Those modules (SQLPS and SQLSERVER) do not come with cmdlets that covers the full spectra of T-SQL commands. Very very far from it. You have backup-sqldatabase and a few others. But in the end you only have a handful of cmdlets in those modules. So if you want to use something which is outside of the cmdlets, then use a 3:rd party (dbatools), Invoke-Sqlcmd or the SMO API (from powershell since ps allow using .Net assemblies). – Tibor Karaszi Nov 22 '18 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.