Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available.
I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key.
What you need to do is to restore the database, open the database master key using the DBMK password and then add the Server 2 SMK encryption to the DBMK:
RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
Not to discount Brent's answer (although he does give some good ones on his training at times)...
In PowerShell you can execute queries that return multiple datasets, you just cannot do this using Invoke-Sqlcmd
as it is not built for it currently.
You would have two options of doing this with either .NET native code (e.g. System.Data.SqlClient
) or using the trusty SMO
. I tend to chose SMO
simply in the event I want to include server properties or something else that SMO
has access to.
In the context of sp_AskBrent
I went ahead and spent a few minutes just building the output into an HTML report. The main points of interest in this script is that to handle multiple datasets you will execute your query with ExecuteWithResults
method which is available under the following namespaces:
Microsoft.SqlServer.Management.Smo.Server
Microsoft.SqlServer.Management.Smo.Database
You can see an example of using the database
namespace on MSDN here. In my script I am using the Server
namespace. It will work either way but if you review the MSDN article the ExecuteWithResults
will return a DataSet
object and that object will contain a DataTable
. The number of DataTables
is based on the number of datasets returned by your code. In the case of Brent's procedure you will get back 5 DataTables
. You could verify this in the code below by adding in $results.Count
, just before the foreach
loop.
Now one other note in this script is I chose to output to HTML. You can chose another format if desired but outputting everything to the console is just not readable. I also will note I added help information so you can use Get-Help
against the script if you need to see details on parameters or reminder yourself how to call it.
I used a little snippet of code from Pinal Dave to just generate some CPU usage on my local instance for this example. (Otherwise the procedure did not return much information).
Script
<#
.SYNOPSIS
Executes sp_AskBrent and outputs to HTML
.DESCRIPTION
Execute sp_AskBrent in normal or expert mode and outputs to an HTML.
.PARAMETER server
String. [REQUIRED] The SQL Server instance you wish to get results on.
.PARAMETER timelimit
Integer. Time used for @seconds parameter of sp_AskBrent
.PARAMETER expertMode
Switch. Just opts to have @ExpertMode = 1 for the query
.PARAMETER sqlversion
Integer. If on machine with multiple SQL Server tool versions installed, specify version.
.PARAMETER outfile
String. Set output file to generate HTML
.EXAMPLE
Run command in normal mode, which returns one dataset
.\Script.ps1 -server MANATARMS\SQL12 -timelimit 5 -sqlversion 11 -outfile 'C:\temp\MyServer.html'
.EXAMPLE
Run command in expert mode, which returns multiple datasets
.\Script.ps1 -server MANATARMS\SQL12 -timelimit 5 -expertMode -sqlversion 11 -outfile 'C:\temp\MyServer.html'
.NOTES
Does not check if sp_AskBrent is on the server before executing the query.
#>
[cmdletbinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[Alias("instance")]
[string]
$server,
[Parameter(Mandatory=$false,Position=1)]
[int]
$timelimit = 10,
[Parameter(Mandatory=$false,Position=2)]
[switch]
$expertMode,
[Parameter(Mandatory=$false,Position=3)]
[int]
$sqlversion = 11,
[Parameter(Mandatory=$false,Position=4)]
[string]
$outfile= 'C:\temp\Testing.html'
)
$HtmlTop = @"
<!DOCTYPE html>
<html>
<head>
<style>
body {
background-color:white;
font-family:Tahoma,Arial;
font-size:10pt;
}
table {
border-collapse: collapse;
}
th {
border:2px solid black;
color:white;
background-color: #0066FF;
padding: 4px;
}
th.subhead {
color:black;
background-color: #8dbafc
}
td {
border: 1px solid black;
padding: 2px;
}
</style>
</head>
<body>
<h2>Server Name: $($server)</h2>
<h3>Run Date: $(Get-Date)</h3>
"@
$HtmlTop | Out-File -FilePath $outfile -Force
if ($expertMode) {
$askBrent = "EXEC sp_AskBrent @Seconds=$($timelimit), @ExpertMode=1"
}
else {
$askBrent = "EXEC sp_AskBrent @Seconds=$($timelimit), @ExpertMode=0"
}
try {
Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=$($sqlversion).0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
}
catch {
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
}
$srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
$results = $srv.ConnectionContext.ExecuteWithResults($askBrent);
foreach ($t in $results.Tables) {
$t | Select-Object * -ExcludeProperty RowError, RowState, HasErrors, Table, ItemArray | ConvertTo-Html -As Table -Fragment | Out-String | Out-File -FilePath $outfile -Append
## This is just to get a break between tables ##
"<hr>" | Out-File -FilePath $outfile -Append
}
Best Answer
You can use dbatools which is powershell based.
For your case you need to have a 2 step approach.
then use
Copy-DbaDatabase
with-BackupRestore
switch. Alternately, you can useBackup-DbaDatabase
and thenRestore-DbaDatabase
as per your needs.