Performance suggestion for compare to backup

ColinBlair

Since the backup is static, it would be nice if only the live database could be refreshed without having to get the schema from the backup again.

Chris Spencer

This sounds like a sensible idea although I'm unsure about the technical feasibility of doing this. However, it has been noted.

We have attempted to make the backup schema retrieval as quick as possible and it should only take a matter of seconds. Out of interest how long is it taking for your backup?

Regards
Chris

richardjm

Also as an aside - can I just check that you're talking about the schema refresh in the Tables & Views tab on the project options?

ColinBlair

To answer your timing question, several minutes. I was reading from a 36GB backup file. I was talking about the schema refresh in the Tables & Views tab. This was the "final archive" backup from a previous version of our system. We discovered that one of the tables (a log table) had lost data during the conversion. Unfortunatly, SQL Data Compare couldn't match up the two tables well enough (the table names had been changed and the primary key was renamed to match the new table name). I got around the problem by creating a table in the current database with the same name and just the columns that needed to be created. That worked but waiting for the resynch to complete just to see that new temp table appear was a bit annoying.

Once the resynch happend everything else worked fine. I pulled 10,564,766 rows of data out of the backup file using SQL Data Compare 6.

Actually, in my situation the option to just supply a new table name and have the entire table copied from the backup into that new table would have been great.

richardjm

You're right then we probably should optimize the schema refresh as you're right we hold a file lock on the backup file so it couldn't possibly change. I'll put that as a fix request for the point release.

You could use mapping to map the table names together and then the columns for the keys if they've changed. Data compare will never create a table as there could be many dependencies which we don't read like filegroups, assemblies for CLR types, partition functions etc. etc. That's really a job for SQL Compare.

HTH

ColinBlair

richardjm wrote:

You're right then we probably should optimize the schema refresh as you're right we hold a file lock on the backup file so it couldn't possibly change. I'll put that as a fix request for the point release.

You could use mapping to map the table names together and then the columns for the keys if they've changed. Data compare will never create a table as there could be many dependencies which we don't read like filegroups, assemblies for CLR types, partition functions etc. etc. That's really a job for SQL Compare.

HTH

I was able to create the map but on the compare screen there no checkbox next to the table pair because there was no key for it to search by. I couldn't figure out how to create a key.

richardjm

If the comparison keys were still the same in essence (same columns, same order, same everything apart from name of the index itself) then it should have picked that up and allowed you to choose one of them. If they had changed at all there is no way to set a custom comparison key when comparing to a backup. The index does have to be unique which could be a sticking point.