Exclude Logins from SQL database compare
dUros
Posts: 21 Bronze 1
Hello
I use Powershell cmdlets to compare two database, generate changes script and execute this script.
Powershell script:
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:
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
How to exclude logins from compare?
Thank you.
Regards
Tagged:
Best Answers
-
Tianjiao_Li Posts: 684 Rose Gold 5You can create a compare filter file to exclude the users https://documentation.red-gate.com/sca/developing-databases/concepts/filtering
-
Tianjiao_Li Posts: 684 Rose Gold 5I'm afraid filter can't be used with -IgnoreAdditional switch.
https://documentation.red-gate.com/sca/reference/powershell-cmdlets/sync-databaseschema
Once I removed it from the script, it appears to work fine for me. Please give it a try.
Answers
Thank you for your post.
I prepare Filter.scpf file with SQL Compare then modified my script:
....
But now get this error:
I try -FilterPath 'D:\Filters\Filter.scpf' and -FilterPath $FilterPath but no difference.
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.
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