1

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

1 Answer 1

2

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

Backup-DbaDbCertificate

Backup-DbaDbMasterKey

Restore-DbaDbCertificate

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

3
  • i dont think, i can install that module due to limitation at work..but sqlps & sqlserver powershell modules are available Nov 21, 2018 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, 2018 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). Nov 22, 2018 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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