Options

Exclude Logins from SQL database compare

dUrosdUros Posts: 21 Bronze 1
in SQL Change Automation
Hello

I use Powershell cmdlets to compare two database, generate changes script and execute this script.
Powershell script:

$Conn1 = New-DatabaseConnection -ServerInstance 'SQL' -Database 'DB1' -Username 'usr1' -Password 'pass1'
$Conn2 = New-DatabaseConnection -ServerInstance 'SQL' -Database 'DB2' -Username 'usr1' -Password 'pass1'
$Options = 'DecryptPost2KEncryptedObjects, DisableAndReenableDdlTriggers, -ObjectExistenceChecks, -ForceColumnOrder, IgnoreUserProperties, IgnoreUsersPermissionsAndRoleMemberships, IgnoreWhiteSpace, UseCompatibilityLevel, IgnoreDatabaseAndServerName, IgnoreIdentitySeedAndIncrement, IgnoreStatistics, IgnoreFillFactor, IgnoreFileGroups, IgnoreWithElementOrder'
$syncResult = Sync-DatabaseSchema -Source $Conn1 -Target $Conn2 -IgnoreAdditional -SQLCompareOptions $Options -AbortOnWarningLevel None

DB1 is local database, DB2 is database from other server (different location, subsidies). Logins and users are different in DB1 and DB2. When execute Powershell script get errors:

WARNING: The error 'Windows NT user or group 'DOMAINDB1\administrator' not found. Check the name again.' occurred when executing the following SQL:
WARNING: IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'DOMAINDB1\administrator')
WARNING: CREATE LOGIN [DOMAINDB1\administrator] FROM WINDOWS
Sync-DatabaseSchema : Windows NT user or group 'DOMAINDB1\administrator' not found. Check the name again.
Local parameters [connectionString = Data Source=SQL;Initial Catalog=DB1;User ID=usr1;Password=********;Applicatio
n Name="SQL Change Automation"]

How to exclude logins from compare?

Thank you.
Regards
Tagged:
· Share on Twitter

Best Answers

Answers

  • Options
    dUrosdUros Posts: 21 Bronze 1
    edited October 16, 2019 2:47PM
    Hello

    Thank you for your post.

    I prepare Filter.scpf file with SQL Compare then modified my script:
    ....
    $FilterPath = 'D:\Filters\Filter.scpf '
    $syncResult = Sync-DatabaseSchema -Source $Conn1 -Target $Conn2 -FilterPath $FilterPath -IgnoreAdditional -SQLCompareOptions $Options -AbortOnWarningLevel None -QueryBatchTimeout 60

    But now get this error:
    SQLCompare.exe terminated with the exit code 8: Unsatisfied argument dependency. 
    Either an argument was missing, or two incompatible arguments were used. 
    Call the command with a valid set of arguments.

    I try -FilterPath 'D:\Filters\Filter.scpf' and -FilterPath $FilterPath but no difference.
    I looking documentation but can't find where I made a mistake.

    Sync-DatabaseSchema -Source <Object> -Target <DatabaseConnection> [-FilterPath <string>] [-IgnoreStaticData] [-IgnoreAdditional] [-SQLCompareOptions <string>] [-SQLDataCompareOptions <string>] [-TransactionIsolationLevel <TransactionIsolationLevel>] [-QueryBatchTimeout <int>] [-AbortOnWarningLevel <WarningSeverity>] [-IgnoreParserErrors] [-SqlCmdVariables <hashtable>] [<CommonParameters>]

    Do you have some example with -FilterPath argument?

    Thanks again.
    · Share on Twitter
  • Options
    dUrosdUros Posts: 21 Bronze 1
    Hello

    Thank you very much for your help and time.
    I didn't see highlighted sentence  :/ though I read documentation a few time. Now comparison work very well.  :)

    Regards
    · Share on Twitter
Sign In or Register to comment.