SQL Compare snapshot commandline - to not decrypt 2008 obj
ricky lively
Posts: 3
What is the snapshot creation commandline option to not decrypt encrypted objects in the PRO version of SQL Compare (or SQL Snapper)?
Recently we upgraded some servers to SQL Server 2008 (everything works fine in 2000 and 2005). I was trying to generate snapshots of the databases but they now timeout with the message: "a transport-level error has occurred when receiving results from the server. (provider: TCP provider, error: 0 - An existing connection was forcibly closed by the remote host.)"
I can successfully make a snapshot in the GUI if I uncheck "decrypt encrypted objects on 2005 and 2008 databases". I want to script out the creation of database snapshots for archival purposes.
(There are also some more GUI options I can't find the commandlines for: "auto detect case sensitivity" and "treat items as case sensitive"... in case I need to use them in the future?)
Recently we upgraded some servers to SQL Server 2008 (everything works fine in 2000 and 2005). I was trying to generate snapshots of the databases but they now timeout with the message: "a transport-level error has occurred when receiving results from the server. (provider: TCP provider, error: 0 - An existing connection was forcibly closed by the remote host.)"
I can successfully make a snapshot in the GUI if I uncheck "decrypt encrypted objects on 2005 and 2008 databases". I want to script out the creation of database snapshots for archival purposes.
(There are also some more GUI options I can't find the commandlines for: "auto detect case sensitivity" and "treat items as case sensitive"... in case I need to use them in the future?)
Comments
/Options:DecryptPost2kEncryptedObjects
Also if you use the command "sqlcompare /verbose /?" will generate a detailed listing of options available.
For example:
"treat items as case sensitive" = /Options:CaseSensitiveObjectDefinition
SQL Compare Command Line V8.1.0.360
==============================================================================
Copyright c Red Gate Software Ltd 1999-2009
Serial Number: 507-001-104031-D9C0
Connecting to database 'MonitorDB' on 'xxxyyy02'...
Error: Error occurred connecting to database: A transport-level error has
occurred when receiving results from the server. (provider: TCP Provider, error:0 - An existing connection was forcibly closed by the remote host.)
it appears the those options are only for comparison/sync and not snapshot creation... they show nothing when /verbose on snapshot, but show the following on compare:
SQL Compare Command Line V8.1.0.360
==============================================================================
Copyright c Red Gate Software Ltd 1999-2009
Serial Number: 507-001-104031-D9C0
SQL Compare running with option: IgnoreConstraintNames (OK).
SQL Compare running with option: IgnoreWhiteSpace (OK).
SQL Compare running with option: IgnoreStatistics (OK).
SQL Compare running with option: IgnoreFillFactor (OK).
SQL Compare running with option: IgnoreFileGroups (OK).
SQL Compare running with option: IgnoreQuotedIdentifiersAndAnsiNullSettings (OK).
SQL Compare running with option: IgnoreUserProperties (OK).
SQL Compare running with option: IgnoreWithElementOrder (OK).
SQL Compare running with option: IgnorePermissions (OK).
SQL Compare running with option: IgnoreUsersPermissionsAndRoleMemberships (OK).
Once again, how do I create a snapshot without decrypted objects so my connection does not timeout?
In your original post, you mention that you are able generate the snapshot using the UI and deselecting the "decrypt encrypted..." option.
As a test could you try running the command line comparison without the "decrypt..." option present at all. So instead of using /options:default, use each constiuent option explicitly. See below:
/Options:IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder
Are you now able to create the snapshot without the timeout?