Using a SQL Backup file for comparison

Comments

• Brian Donahue Posts: 6,590 Bronze 1

  November 23, 2008 3:24PM

  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.

  0 · Share on Twitter
• meklembl Posts: 107 Bronze 2

  November 24, 2008 10:18AM

  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.
  Thanks,
  Larry

  Larry M
  Cleveland, OH USA

  0 · Share on Twitter
• Robert C Redgate › Posts: 425 Silver 2

  November 24, 2008 10:28AM

  Hi,
  
  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,
  Rob

  Robert Chipperfield
  Red Gate

  0 · Share on Twitter