Data Compare Two Database Backups

zaliiszaliis Posts: 14
edited July 4, 2014 5:28AM in SQL Comparison SDK Previous Versions
I am trying to do a data compare between two sql server backups. the TableDifferences collection is always empty. I know there are differences because I added six records myself and had our DBA check both backups using his RedGate client. Could you please look over this code and see if I made any mistakes. 
    public class RedgateTester
    {
        public void CompareDatabaseBackups(String sourceBackupFile, String targetBackupFile)
        {
            using (Database dbSource = GetBackupDatabaseSource(false, sourceBackupFile))
            {
                using (Database dbTarget = GetBackupDatabaseSource(false, targetBackupFile))
                {
                    Differences redgateDifferences = dbSource.CompareWith(dbTarget, Options.Default);
                    Boolean schemaDifferences = redgateDifferences.Any(e => e.Type != RedGate.Shared.ComparisonInterfaces.DifferenceType.Equal);
                    if (schemaDifferences)
                    {
                        throw new Exception("There are Schema Differences here.  Data Compare will not occur.");
                    }
                }
            }
            using (Database dbSource = GetBackupDatabaseSource(true, sourceBackupFile))
            {
                using (Database dbTarget = GetBackupDatabaseSource(true, targetBackupFile))
                {
                    using (ComparisonSession csComparison = new ComparisonSession())
                    {
                        SchemaMappings smMappings = new SchemaMappings();
                        smMappings.CreateMappings(dbSource, dbTarget);
                        foreach (TableMapping tmMapping in smMappings.TableMappings)
                        {
                            tmMapping.Include = true;
                        }
                        csComparison.CompareDatabases(dbSource, dbTarget, smMappings);

                        foreach (TableDifference difference in csComparison.TableDifferences)
                        {
                            if (difference.TableMapping.Include) // included difference
                            {
                                DifferencesSummary summary = difference.DifferencesSummary;

                                Console.WriteLine(String.Format("Table {0} ", difference.TargetTable(true).Name));
                                Console.WriteLine(String.Format("Live Only {0}", summary.DifferenceCount(Row.RowType.In1)));
                                Console.WriteLine(String.Format("Script Folder Only {0}", summary.DifferenceCount(Row.RowType.In2)));
                                Console.WriteLine(String.Format("In both but Different {0}", summary.DifferenceCount(Row.RowType.Different)));
                                Console.WriteLine(String.Format("In both and the Same {0}", summary.DifferenceCount(Row.RowType.Same)));
                            }
                        }
                    }
                }
            }
        }
        BackupSetDatabase GetBackupDatabaseSource(Boolean registerForData, 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]);
            Console.WriteLine("Registering backup " + files[0]);
            if (registerForData)
            {
                backupDatabase.RegisterForDataCompare(backupDatabaseSource.ToConnectionProperties(), Options.Default);
            }
            else
            {
                backupDatabase.Register(backupDatabaseSource.ToConnectionProperties(), Options.Default);
            }
            return backupDatabase;
        }
    }
· Share on Twitter

Comments

  • rhenckerhencke Posts: 7
    I've been struggling with a similar issue between comparing a backup between a backup and a live database.

    Even using the stock sample code with no modifications (and some row changes on WidgetLive), I get absolutely no differences - the TableMappings collection is always empty.

    I'm starting to think that using backup comparison is simply broken in this version.
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    I'm not aware of any problem with the backup reader asides from the usual performance issues (it's likely to be slower and less reliable than comparing to a DB directly)

    The first thing I'd check is what the Data Compare GUI shows- we can only compare tables that have something unique to work with; basically a PK or a unique index.

    If the Data Compare GUI seems ok, then it sounds like there's something odd going on so we can do some more testing.
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • zaliiszaliis Posts: 14
    Its not my tables I made them really simple in both my source and target databases. See Below
    USE [TargetTestCompare]
GO

/****** Object:  Table [dbo].[Table_1]    Script Date: 4/16/2014 8:39:38 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_1](
	[Column1] [int] NOT NULL,
	[Column2] [int] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[Column1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [TargetTestCompare]
GO

/****** Object:  Table [dbo].[Table_2]    Script Date: 4/16/2014 8:39:45 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_2](
	[Column1] [int] NOT NULL,
	[Column2] [int] NOT NULL,
	[Column3] [int] NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
	[Column2] ASC,
	[Column1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table_2]  WITH CHECK ADD  CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([Column1])
REFERENCES [dbo].[Table_1] ([Column1])
GO

ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]
GO
    · Share on Twitter
  • zaliiszaliis Posts: 14
    By the way I think this issue is related to a previous post I entered about not being able to read data from a backup. I have tried everything I can think of and finally gave up. James it sounds like you have the ability to read from a backup could you post a small code sample.

    Thanks.
    · Share on Twitter
  • rhenckerhencke Posts: 7
    Dear James,

    Thank you for your reply. Per your advice, I attempted the same comparison using SQL Data Compare 10.4. This worked correctly for both emulating WidgetDev->WidgetLive comparison, as well as my own situation.

    Here are the steps to reproduce the failure in the example included in SQL Comparison SDK 1.5:

    1. Install SQL Comparison SDK 10 (10.5.0.7)
    2. Open "C:\Program Files (x86)\Red Gate\SQL Comparison SDK 10\GettingStarted.exe" and click "SQL creation script for sample databases"
    3. Logged in under an administrative account, run the supplied script on SQL Server (for reference, I have tried this on SQL Server 2008 (10.0.2531) and SSDT's LocalDB (11.0.3000))
    4. Open the C# SQL Data Compare API samples folder from the GettingStarted.exe
    5. Open Visual Studio 2008 as an Administrator (so it has write access to the installation folder)
    6. Open "SQLDataCompareCodeSnippets.csproj" in the newly opened Visual Studio instance.
    7. Edit Program.cs, changing the DevServerName and LiveServerName to the server from step 3.
    8. Run the example program.
    9. Run option c) Backup Comparison.

    Here is where the first problem occurs. A DllNotFound exception is thrown on line 37 of BackupComparisonExample.cs:
    Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
    I worked around this by grabbing version 1.0.85.0 of Sqlite.Interop.dll from http://system.data.sqlite.org/downloads/1.0.85.0/sqlite-netFx35-static-binary-Win32-2008-1.0.85.0.zip, and placing Sqlite.Interop.dll in the bin/Debug/x86/ folder as a temporary workaround.

    At this point, the example runs, but no differences are found. Placing a breakpoint on line 65 of BackupComparisonExample.cs, you can inspect session.TableDifferences and see it contains no items.
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    I've just been setting up a test here, and you're quite right- the problem seems to be that the TableDifferences property of the session is empty, even though the mappings have been created.

    I'll see if I can work out anything
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    I've just been setting up a test here, and you're quite right- the problem seems to be that the TableDifferences property of the session is empty, even though the mappings have been created.

    I'll see if I can work out anything
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • rhenckerhencke Posts: 7
    Thank you, James. Your assistance is much appreciated!
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    I'm going to escalate this to our developers to see if they can work out the cause.
    I have tested with a set of script folders too and these seem to work OK, so you might want to try that as an alternative.

    You'd basically change the registration from the Backup code to:
    using (Database backupDB= new Database())
    backupDB.RegisterForDataCompare("d:\\WidgetTest", new RedGate.SQLCompare.Engine.ReadFromFolder.ScriptDatabaseInformation(), Options.Default);

    The rest is pretty much the same. You can create scripts of the schema using SQL Compare (and the SDK has a snippet example I think), and SQL Data Compare will sync from the DB > Script folder to populate it. That folder can then be deployed with an SDK app instead of a backup.

    I'll post back as and when I get a theory on the Backup file.
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • rhenckerhencke Posts: 7
    James,

    I'll give scripting the backup database file schema to a folder a shot as a workaround. Thank you for the idea and for researching this.
    · Share on Twitter
  • rhenckerhencke Posts: 7
    Dear James,

    Have you heard anything from the developers on this? Would my best bet possibly be to file a support request?

    Thank you.
    Robert
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    Nothing concrete- my guess is that it's indeed a bug. I'll get one logged in our Jira system for it. Unfortunately the Compare team are currently working flat out on SQL 2014 support and I'm not sure when they are next checking into the SDK, so I can't give an eta on when this is likely to get looked at I'm afraid.

    *edit - I've logged this under ref. CSD-191
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • rhenckerhencke Posts: 7
    Got it - thank you for the update and for looking into this.
    · Share on Twitter
  • rhenckerhencke Posts: 7
    For anyone else hitting this, downgrading to SQL Comparison SDK 10.0 (if that's an option for you) will fix this.
    · Share on Twitter
  • davidlidavidli Posts: 1
    The car audio wholesale ,you c an have a look at
    · Share on Twitter
Sign In or Register to comment.