Comparing and Scripting SQL Server Database Objects using PowerShell and SQLPSX


Today I had to compare two databases on different servers, looking for views on the server1 and not were  in a server2.

I used the compare-object cmdlet :

$Source = Get-SqlDatabase -sqlserver ServerSource -dbname DB1 | Get-SqlView 

$Dest = Get-SqlDatabase -sqlserver ServerDest -dbname DB2 | Get-SqlView

 

$DiffViews = Compare-Object -DifferenceObject $dest `

                            -ReferenceObject $Source `

                            -Property Name | ?{$_.SideIndicator -eq '<='} | % {$_.Name}

                            

$scriptingOptions = New-SqlScriptingOptions

$scriptingOptions.Permissions = $true

$scriptingOptions.IncludeIfNotExists = $true

get-sqldatabase -sqlserver ServerSource-dbname DB2| Get-SqlView | ?{$DiffViews -contains $_.name} | Get-SqlScripter -scriptingOptions $scriptingOptions | Out-File c:\temp\DiffViews.sql

 

Then, was just use Get-SqlScripter to Script the difference and out to c:\temp\DiffViews.sql

Thanks to Sir Chad Miller to help me in the final script Alegre

Simple, Fast and Clean. Classic PowerShell

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s