Sql comparison from backups

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Sql comparison from backups

Postby zaliis » Wed Mar 19, 2014 5:45 pm

I am trying to perform a data compare with both the source and target databases both be backups. When I enumerate the TableDifferences collection it is empty. Can anyone see what I am doing wrong in the code? I am using latest version of the SDK.

Code: Select all
        static void Test2()
        {
            Database sourceDatabase = GetBackupDatabaseSource(@\"D:\\Database Compare Project\\DataBases\\Source\\FPM-FSE6-UserData_Db_201403122016_full.BAK\");
            //Database targetDatabase = GetBackupDatabaseSource(@\"D:\\Database Compare Project\\DataBases\\Target\\FPM-Production-UserData_Db_201403122016_full.BAK\");
            Database targetDatabase = GetBackupDatabaseSource(@\"D:\\Database Compare Project\\DataBases\\Target\\FPM-Production-RateSynch_Db_201403122016_full.BAK\");
            // Create the schema mappings used to compare the database
            SchemaMappings mappings = new SchemaMappings();
            mappings.CreateMappings(sourceDatabase, targetDatabase);
            using (ComparisonSession session = new ComparisonSession())
            {
                session.Status += new StatusEventHandler(StatusCallback);
                session.CompareDatabases(sourceDatabase, targetDatabase, mappings);

                foreach (TableMapping mapping in mappings.TableMappings)
                {
                    TableDifference difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];
                    if (difference != null)
                    {
                        using (Reader different = difference.ResultsStore.GetReader(Row.RowType.All))
                        {
                            while (different.GetEnumerator().MoveNext())
                            {
                                Row currentRow = different.GetRow(0);
                            }
                        }
                    }

                }
            }
        }
        static BackupSetDatabase GetBackupDatabaseSource(params String[] backupFiles)
        {
            List<string> files = new List<string>();
            files.AddRange(backupFiles);
            BackupSetDatabase backupDatabase = new BackupSetDatabase();
            IList<RedGate.Shared.SQL.BackupReaderInterfaces.IBackupSet> backupSets = backupDatabase.GetBackupSets(files, null);
            String[] passwords = new string[0];
            BackupDatabaseSource backupDatabaseSource = new BackupDatabaseSource(files, passwords, backupSets[0]);
            //backupDatabaseSource.Files = files;

            backupDatabase.Status += new StatusEventHandler(StatusCallback);
            Console.WriteLine(\"Registering backup \" + files[0]);
            try
            {
                //backupDatabase.Register(backupDatabaseSource.ToConnectionProperties(), Options.Default);
                backupDatabase.RegisterForDataCompare(backupDatabaseSource.ToConnectionProperties(), Options.Default);
            }
            catch (RedGate.BackupReader.SqbReader.PasswordProtectedException)
            {
                /* If we have reached this block, the backup is an encrypted
                  * SQL Backup file and we must ask for the password!
                  * NB the decryption requires RedGate.BackupReader.CryptoHelper.dll
                  * and zlib1.dll to be copied into your output directory */
                Console.WriteLine(\"This SQL Backup file is password protected.\\r\\nPlease enter the password:\");
                string sqbPassword = Console.ReadLine();
                backupDatabaseSource.Passwords.Add(sqbPassword);
                backupDatabase.Register(backupDatabaseSource.ToConnectionProperties(), Options.Default);
            }
            catch (RedGate.BackupReader.BackupReaderException brx)
            {
                // If we have reached this block, something generally bad has happened.
                Console.WriteLine(\"Could not register backup: \" + brx.Message);
            }
            return backupDatabase;
        }
zaliis
 
Posts: 8
Joined: Tue Mar 18, 2014 10:05 pm

I have it working

Postby zaliis » Wed Mar 19, 2014 6:25 pm

Modified the code as follows and I am now getting differences.


Code: Select all
        static void Test2()
        {
            Database sourceDatabase = GetBackupDatabaseSource(@"D:\\Database Compare Project\\DataBases\\Source\\FPM-FSE6-UserData_Db_201403122016_full.BAK");
            Database targetDatabase = GetBackupDatabaseSource(@"D:\\Database Compare Project\\DataBases\\Target\\FPM-Production-RateSynch_Db_201403122016_full.BAK");
            Differences diffs = sourceDatabase.CompareWith(targetDatabase, Options.Default);
            System.Diagnostics.Debugger.Break();
        }
        static BackupSetDatabase GetBackupDatabaseSource(params String[] backupFiles)
        {
            List<string> files = new List<string>();
            files.AddRange(backupFiles);
            BackupSetDatabase backupDatabase = new BackupSetDatabase();
            IList<RedGate.Shared.SQL.BackupReaderInterfaces.IBackupSet> backupSets = backupDatabase.GetBackupSets(files, null);
            String[] passwords = new string[0];
            BackupDatabaseSource backupDatabaseSource = new BackupDatabaseSource(files, passwords, backupSets[0]);
            //backupDatabaseSource.Files = files;

            backupDatabase.Status += new StatusEventHandler(StatusCallback);
            Console.WriteLine("Registering backup " + files[0]);
            try
            {
                //backupDatabase.Register(backupDatabaseSource.ToConnectionProperties(), Options.Default);
                backupDatabase.RegisterForDataCompare(backupDatabaseSource.ToConnectionProperties(), Options.Default);
            }
            catch (RedGate.BackupReader.SqbReader.PasswordProtectedException)
            {
                /* If we have reached this block, the backup is an encrypted
                  * SQL Backup file and we must ask for the password!
                  * NB the decryption requires RedGate.BackupReader.CryptoHelper.dll
                  * and zlib1.dll to be copied into your output directory */
                Console.WriteLine("This SQL Backup file is password protected.\\r\\nPlease enter the password:");
                string sqbPassword = Console.ReadLine();
                backupDatabaseSource.Passwords.Add(sqbPassword);
                backupDatabase.Register(backupDatabaseSource.ToConnectionProperties(), Options.Default);
            }
            catch (RedGate.BackupReader.BackupReaderException brx)
            {
                // If we have reached this block, something generally bad has happened.
                Console.WriteLine("Could not register backup: " + brx.Message);
            }
            return backupDatabase;
        }
zaliis
 
Posts: 8
Joined: Tue Mar 18, 2014 10:05 pm


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 1 guest