Using a SQL Backup file for comparison

meklemblmeklembl Posts: 107 Bronze 2
edited November 24, 2008 10:28AM in SQL Data Compare Previous Versions
Where is the 'work' done for retrieving items/data out of a file created from SQL Backup?

Should I expect time for the retrieval to take as long as a restore would take?
Larry M
Cleveland, OH USA


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Larry,

    Restoring a single table shouldn't take as long as restoring the whole backup file. The way it's been explained to me is that Data Compare implements a "page reader" that dumps the pages for individual tables. Once the initial read of the backup's schema is done, it is relatively quick to restore a single table because only the table's pages are dumped from the backup sequentially.
  • Options
    meklemblmeklembl Posts: 107 Bronze 2
    That makes sense, but the reason I ask the first question is because the tables that are the basis for the compare comprise about 25% of the space for the database. So I began to wonder, where this data was being put in respect to the server and or client. The backup file is a compressed SQL Backup file located on a networked file server.
    Larry M
    Cleveland, OH USA
  • Options

    The comparison is done by the SQL Data Compare client - so in the case of a live database, it pulls the rows back from SQL Server, and in the case of a backup file, it'll read the contents of the file back to the client app as well. During a comparison, the data is stored on disk in the temporary directory of the machine running SQL Data Compare.

    For backup files, the data never goes to SQL Server at all - we read the file directly.

    As for speed, I'd recommend using a clustered index as your comparison key if at all possible - it's quite significantly faster when reading backup files.

    Hope that helps,
    Robert Chipperfield
    Red Gate
Sign In or Register to comment.