Red Gate forums :: View topic - Data Compare Two Database Backups
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

Data Compare Two Database Backups

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
zaliis



Joined: 18 Mar 2014
Posts: 8

PostPosted: Mon Apr 14, 2014 11:16 pm    Post subject: Data Compare Two Database Backups Reply with quote

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:

    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;
        }
    }
Back to top
View user's profile Send private message
rhencke



Joined: 16 Apr 2014
Posts: 7

PostPosted: Wed Apr 16, 2014 1:42 am    Post subject: Re: Data Compare Two Database Backups Reply with quote

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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Wed Apr 16, 2014 12:20 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
zaliis



Joined: 18 Mar 2014
Posts: 8

PostPosted: Wed Apr 16, 2014 3:41 pm    Post subject: Data Compare Two Database Backups Reply with quote

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

Code:

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
Back to top
View user's profile Send private message
zaliis



Joined: 18 Mar 2014
Posts: 8

PostPosted: Wed Apr 16, 2014 3:47 pm    Post subject: Data Compare Two Database Backups Reply with quote

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.
Back to top
View user's profile Send private message
rhencke



Joined: 16 Apr 2014
Posts: 7

PostPosted: Wed Apr 16, 2014 5:02 pm    Post subject: Reply with quote

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:
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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Wed Apr 16, 2014 5:31 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Wed Apr 16, 2014 5:37 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
rhencke



Joined: 16 Apr 2014
Posts: 7

PostPosted: Wed Apr 16, 2014 5:52 pm    Post subject: Reply with quote

Thank you, James. Your assistance is much appreciated!
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Wed Apr 16, 2014 7:43 pm    Post subject: Reply with quote

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:

Quote:

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.
Back to top
View user's profile Send private message
rhencke



Joined: 16 Apr 2014
Posts: 7

PostPosted: Wed Apr 16, 2014 8:38 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
rhencke



Joined: 16 Apr 2014
Posts: 7

PostPosted: Fri Apr 18, 2014 1:56 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Apr 21, 2014 7:34 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
rhencke



Joined: 16 Apr 2014
Posts: 7

PostPosted: Mon Apr 21, 2014 2:12 pm    Post subject: Re: Reply with quote

Got it - thank you for the update and for looking into this.
Back to top
View user's profile Send private message
rhencke



Joined: 16 Apr 2014
Posts: 7

PostPosted: Mon Apr 21, 2014 8:44 pm    Post subject: Reply with quote

For anyone else hitting this, downgrading to SQL Comparison SDK 10.0 (if that's an option for you) will fix this.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group