SQL Compare from powershell

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

SQL Compare from powershell

Postby philcart » Thu Mar 29, 2012 3:07 am

I'm putting together a powershell script to help us deploy a changeset from TFS to SQL Server.

As the changeset is only a very small subset of the database objects, in some cases only a single procedure, I'm using the /include switch to only compare the objects in the changeset.

eg: the command line that is built up in Powershell looks like the following,

Code: Select all
&"C:\\Program Files (x86)\\Red Gate\\SQL Compare 10\\SQLCompare.exe" /Scripts1:"C:\\Temp\\2583\\SQL" /server2:SQL01 /database2:DB1 /scriptfile:"C:\\Temp\\2583\\2583.sql" /options:default,IgnorePermissions,IgnoreUsersPermissionsAndR
oleMemberships /include:Different  /include:StoredProcedure:[eft_ListBrokenEfts]^|[app_CheckRptServer]^|[SendEmailGeneric]


When I run this from the command prompt, it works as expected and the script file is generated with the correct information.

However, when I use Invoke-Expression within Powershell I get the following error,
"Invoke-Expression : Expressions are only allowed as the first element of a pipeline."

This is because of the pipe characters used in the /include switch.

So I re-jigged my script to do the includes individually as per,

Code: Select all
&"C:\\Program Files (x86)\\Red Gate\\SQL Compare 10\\SQLCompare.exe" /Scripts1:"C:\\Temp\\2583\\SQL" /server2:UATMRISQL01 /database2:KFProd /scriptfile:"C:\\Temp\\2583\\2583.sql" /options:default,IgnorePermissions,IgnoreUsersPermissionsAndR
oleMemberships /include:Different  /include:StoredProcedure:[eft_ListBrokenEfts] /include:StoredProcedure:[app_CheckRptServer] /include:StoredProcedure:[SendEmailGeneric]


This seemed to work, but the script generated included revoking permissions on the included procedures. I can't have the script revoking permissions as they are set specifically in each environment.

Anyone got any ideas on how I progress?


Cheers
Phil
philcart
 
Posts: 32
Joined: Mon Aug 14, 2006 6:14 am

Postby philcart » Thu Mar 29, 2012 4:01 am

I've solved my "Expressions are only allowed as the first element of a pipeline." error by escaping the pipe character with a backtick.

But that still leaves me with a deployment script messing around with permissions.
philcart
 
Posts: 32
Joined: Mon Aug 14, 2006 6:14 am

Postby Brian Donahue » Mon Apr 02, 2012 11:15 am

The IgnorePermissions option should, as the name implies, cause the program to stop scripting permissions. What kinds of permissions are still being scripted in the migration script?
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby philcart » Mon Apr 02, 2012 11:20 pm

Brian

In our TEST/UAT environments there are additional roles/users that have permissions on the procedures being deployed. As such we don't want these permissions reverting to what is set in the DEV environment.

The script that is generated contains the following,
Code: Select all
PRINT N'Altering permissions on [dbo].[SendEmailGeneric]'
GO
REVOKE EXECUTE ON  [dbo].[SendEmailGeneric] TO [ApplicationSupport]
GO


I've tried various switches/options, but it seems explicitly including items overrides the ignore options.

I would expect the included items to be compared as per the specified switches and options.


Cheers
Phil
philcart
 
Posts: 32
Joined: Mon Aug 14, 2006 6:14 am

Postby Brian Donahue » Wed Apr 04, 2012 3:29 pm

Can you try removing the "default" from the options and explicitly stating the options that you want on the command line? I'm told there may a bug in that area.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby philcart » Thu Apr 05, 2012 4:02 am

Brian

I've replaced the "default" option so my command line now looks like,
Code: Select all
&"C:\\Program Files (x86)\\Red Gate\\SQL Compare 10\\SQLCompare.exe" /Scripts1:"C:\\Temp\\2583\\SQL" /server2:UATMRISQL01 /database2:KFProd /scriptfile:"C:\\Temp\\2583\\2583.sql" /options:DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,IgnorePermissions,IgnoreUsersPermissionsAndRoleMemberships /include:Different  /include:StoredProcedure:[eft_ListBrokenEfts]^|[app_CheckRptServer]^|[SendEmailGeneric]



But I still get the "REVOKE" statements included in the script that is generated.


Thanks
Phil
[/code]
philcart
 
Posts: 32
Joined: Mon Aug 14, 2006 6:14 am

Postby Brian Donahue » Thu Apr 12, 2012 3:17 pm

Hi Phil,

I've been told this is a known issue and will be fixed in version 10.2.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby philcart » Fri Apr 13, 2012 12:34 am

Thanks Brian, eagerly awaiting that update :)
philcart
 
Posts: 32
Joined: Mon Aug 14, 2006 6:14 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest