Data Compare Two Database Backups

zaliis

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;
        }
    }

rhencke

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.

James B

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.

zaliis

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

zaliis

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.

rhencke

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.

James B

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

James B

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

rhencke

Thank you, James. Your assistance is much appreciated!

James B

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.

rhencke

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.

rhencke

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

James B

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

rhencke

Got it - thank you for the update and for looking into this.

rhencke

For anyone else hitting this, downgrading to SQL Comparison SDK 10.0 (if that's an option for you) will fix this.

davidli

The car audio wholesale ,you c an have a look at