SQL Server 2014 SP2, backup and restore with powershell

Question

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

Answer 1

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.

• Waqas Sarwar MVP: i dont think, i can install that module due to limitation at work..but sqlps & sqlserver powershell modules are available
• Kin Shah: Then the only option that you have is to code something that is already out by your self
• Tibor Karaszi: 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).