Options
Using a SQL Backup file for comparison
meklembl
Posts: 107 Bronze 2
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?
Should I expect time for the retrieval to take as long as a restore would take?
Larry M
Cleveland, OH USA
Cleveland, OH USA
Comments
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.
Thanks,
Larry
Cleveland, OH USA
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
Red Gate