Geeks With Blogs
David Douglass .NET on My Mind

My project is on the move, literally.  We're migrating from our old data center to a new one; everything is being brought up on new hardware.  Since we're using full and incremental backups, an important sanity check is to make sure all the data has been restored, which is being done using the following query:

select 'select ''' + TABLE_SCHEMA + ''' as TableSchema, ''' + TABLE_NAME + ''' as TableName, count(*) as [Rows] from ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' union' as [SQL] from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

The query above produces a query.  When run against the AdventureWorks database you get:

select 'Production' as TableSchema, 'ProductProductPhoto' as TableName, count(*) as [Rows] from Production.ProductProductPhoto union -- many rows removed select 'Production' as TableSchema, 'ProductPhoto' as TableName, count(*) as [Rows] from Production.ProductPhoto union

The result isn't perfect.  The last union operator needs to be replaced by an order by:

select 'Production' as TableSchema, 'ProductPhoto' as TableName, count(*) as [Rows] from Production.ProductPhoto order by TableSchema, TableName

When you run the fixed up query you'll get a list of every table in a database along with the number of rows in each table:

image

If there's too many tables to compare by hand, save the results from the source and destination databases to text files and compare using WinMerge.

Posted on Friday, November 28, 2008 8:43 AM | Back to top


Comments on this post: Quick Database Sanity Check

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © David Douglass | Powered by: GeeksWithBlogs.net