Thursday, February 16, 2017

[Powershell] scripting out a servers via Red-Gate's SQL Compare

Grant Fritchey posted a basic powershell script that uses Red-Gate's SQL Compare to script out a database (  When I did it, it skipped partitioning and compression, so I wanted to put down my actual script somewhere for the next time I need this.  Specifically, "/options:none", so everything gets included.

Now that I've done it, I'm looking at the resulting folder structure - which doesn't match the structure I already have in place, using a different script (PoSH + SMO).  But I've written it, so here you go.

set-Location "c:\Program Files (x86)\Red Gate\SQL Compare 12\";
. C:\powershell_scripts\invoke-sqlcmd2.ps1

$Servers = "ser-ver-a","ser-ver-b"
$Path = "C:\temp\decom_server_scripting_via_redgate"

foreach ($server in $servers){
Invoke-Sqlcmd2 -Query "sp_databases" -ServerInstance $Server | ForEach-Object {Invoke-Expression "./sqlcompare.exe /s1:$Server /db1:$($_.DATABASE_NAME) /mkscr:$($Path)\$($Server)\$($_.DATABASE_NAME) /options:none" }

No comments: