Comparing record counts from a restore test

Question

Comparing record counts from a restore test

Wayne West

SSC-Insane

Points: 22586
More actions
May 7, 2007 at 10:41 am

#176185
I wrote the following code to make it easier to compare record counts between a live database and a copy restored to test my backups. I thought people might find it useful. Obviously you need to run it against your live side when you do the backup that you're going to restore and compare against.
```
print '--delete the final UNION before the ORDER BY'
print '--'
go
```
```
exec sp_msforeachtable @command1="print 'select ''?'' as TableName, count(*) as TblRowCount from ?' print 'union'"go
print 'ORDER BY TableName'
go
```
Run the script, and you get output something like this:
```
--delete the final UNION before the ORDER BY
--
select '[dbo].[Qualifiers]' as TableName, count(*) as TblRowCount from [dbo].[Qualifiers]
union
select '[dbo].[Questions_SanJose]' as TableName, count(*) as TblRowCount from [dbo].[Questions_SanJose]
union
select '[dbo].[Questions_SanJose2]' as TableName, count(*) as TblRowCount from [dbo].[Questions_SanJose2]
union
etc.
```
Copy the output into another Query Analyzer window and run it, instant record counts. The final result is something like this:
```
TableNameTblRowCount
[dbo].[Authorities]6
[dbo].[Categories]0
[dbo].[Cert_dev]0
[dbo].[Certification Types] 11
```
Suck it into Excel, run it against the restore, and you should be able to see any problems fairly quickly. I would expect perhaps a minor variation in counts, this should at least show you any radical changes. I've only tested this under 2000, I really hope the ForEachTable and ForEachDatabase procs continue in future versions of SQL Server!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Thanks for sharing this.

Care to send this in the scripts section of the site? That way It'll be more accessible to the users who might need this info.

Wayne West SSC-Insane Points: 22586 More actions · Answer 2

I don't know why, but it didn't occur to me. Thanks for the idea!

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That's why 2 heads are better than 1 .

Happy posting.

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 4

Hi,

why not just use

exec sp_msforeachtable @command1="print 'select ''?'' as TableName, count(*) as TblRowCount from ?' print 'union'"

and forget about replacing !?

regards

karl

Best regards
karl

Wayne West SSC-Insane Points: 22586 More actions · Answer 5

Thanks, Karl. I used the exclamation as an escape because I was having problems getting the double apostrophes working. I have no idea what I did wrong as your change works fine!

Now to update the original message and script solution!

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]