What are the challenges you face when working across database platforms? Take the survey
Options

Sql comparison from backups

zaliiszaliis Posts: 14
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.
        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;
        }

Comments

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

            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;
            }
    
Sign In or Register to comment.