Data Compare Two Database Backups

Automate and integrate using the SQL comparison API

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

Data Compare Two Database Backups

Postby zaliis » Mon Apr 14, 2014 11:16 pm

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.
Code: Select all
    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;
        }
    }
zaliis
 
Posts: 8
Joined: Tue Mar 18, 2014 10:05 pm

Re: Data Compare Two Database Backups

Postby rhencke » Wed Apr 16, 2014 1:42 am

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.
rhencke
 
Posts: 7
Joined: Wed Apr 16, 2014 1:37 am

Postby james.billings » Wed Apr 16, 2014 12:20 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Data Compare Two Database Backups

Postby zaliis » Wed Apr 16, 2014 3:41 pm

Its not my tables I made them really simple in both my source and target databases. See Below

Code: Select all
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
 
Posts: 8
Joined: Tue Mar 18, 2014 10:05 pm

Data Compare Two Database Backups

Postby zaliis » Wed Apr 16, 2014 3:47 pm

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.
zaliis
 
Posts: 8
Joined: Tue Mar 18, 2014 10:05 pm

Postby rhencke » Wed Apr 16, 2014 5:02 pm

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:

Code: Select all
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.
rhencke
 
Posts: 7
Joined: Wed Apr 16, 2014 1:37 am

Postby james.billings » Wed Apr 16, 2014 5:31 pm

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.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby james.billings » Wed Apr 16, 2014 5:37 pm

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.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby rhencke » Wed Apr 16, 2014 5:52 pm

Thank you, James. Your assistance is much appreciated!
rhencke
 
Posts: 7
Joined: Wed Apr 16, 2014 1:37 am

Postby james.billings » Wed Apr 16, 2014 7:43 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby rhencke » Wed Apr 16, 2014 8:38 pm

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
 
Posts: 7
Joined: Wed Apr 16, 2014 1:37 am

Postby rhencke » Fri Apr 18, 2014 1:56 pm

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
rhencke
 
Posts: 7
Joined: Wed Apr 16, 2014 1:37 am

Postby james.billings » Mon Apr 21, 2014 7:34 am

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
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Re:

Postby rhencke » Mon Apr 21, 2014 2:12 pm

Got it - thank you for the update and for looking into this.
rhencke
 
Posts: 7
Joined: Wed Apr 16, 2014 1:37 am

Postby rhencke » Mon Apr 21, 2014 8:44 pm

For anyone else hitting this, downgrading to SQL Comparison SDK 10.0 (if that's an option for you) will fix this.
rhencke
 
Posts: 7
Joined: Wed Apr 16, 2014 1:37 am

Next

Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests