October 25, 2007 at 12:25 am
Hi,
I am running SQL Server 2005 Enterprise Edition on Windows 2003 Server Enterprise Edition. I have restored a full database backup and several transaction logs from the live SQL Server instance to a test SQL Server instance. Our client wants several reports to prove that the data on both instances in consistent and there are no missing rows.
The live and test SQL Server instances are in seperate domains and there is no connection between the two, therefore I will be executing the follow against the two instances:
1. Script to return row count of all tables.
2. Script to return the last 100 records for high transaction tables.
3. Script to return 100 records from within the high transaction tables.
4. List of stored procedures/user accounts/views.
My question is, can anyone suggest/recommend any additional checks/reports I can execute?
Thanks in advance.
www.sqlAssociates.co.uk
October 25, 2007 at 9:09 am
If there is something you could count/aggregate to give some idea that things are consistent. e.g. sum of all orders, or averages of the time fields, etc....
something deterministic, so that the numbers should be exactly the same (make sure you use something precise, i.e. no float operations).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 25, 2007 at 9:13 am
Could you not do this with either the full blown copy if you have one, or a demo copy of SQL compare http://www.red-gate.com/products/SQL_Data_Compare/index.htm or any of the other numerous data compare tools out there?
From what I remember most of them produce a fairly granular report about similarities and differences or whatnot.
Hope this helps.
-Luke.
October 25, 2007 at 9:18 am
Hi,
Thanks for your comments, we are unable to use any third pary comparison tools, firsty due to the client not allowing this, and secondly due to the fact there isn't a connection between the two SQL Server instances.
Thanks.
www.sqlAssociates.co.uk
October 25, 2007 at 3:09 pm
Most of the comparison tools I have seen don't require a dirrect connection between the 2 servers. As long as the machine running the software .. say your development box .. then they manage just fine.
One other checkpoint you could do is total the number of minutes (use datepart) from a lastupdate field, or if you don't have one a create field.
I would however be concerned if I was you about your numbers ever matching. You are trying to compare data from an ever changing production environment with a relativly static test environment. Even if you keep moving over transaction logs you are still going to have some latency.
All of that being said (and I say this with all due respect) your client seems to be a bit unreasonable. A test server (even one where you sync the data on a regular basis with production) is never going to be exactly the same as production. As I said above unless you turn off your production server, perform your backups, restore to test, then run your comparisons you are never going to have an exact match.
You may be better off trying to manage your clients expectation than trying to prove that Train A going 50 miles an hour is exactly the same as Train B going 5mph.
Just my opinion though.
Kenneth Fisher
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 26, 2007 at 5:37 am
Hi
Kenneth seems to right. you will be able to do this only if the production db and the db where you are going to restore is the same..... whihc means production db shud not have any additional data modifications.
One way I see is to have a cut-off date uptill which there is the same data in both the dbs. then run your queries byb giving the same date range which is within the cut-off date.
"Keep Trying"
October 26, 2007 at 5:47 am
Tryour problem with Data Publishing Wizard
October 26, 2007 at 8:14 am
there are several things you can do scripting wise
select object_name(id),rowcnt from sysindexes where indid<2
is a quick way to geta row count for all tables
you can also "select object_name(id),text from syscomments" - post into excel and then put formaula in excel to compare the text columns
MVDBA
October 28, 2007 at 1:52 am
Chris,
This is a script we use to perform different actions on certain tables.
I've modified it a little so you can get the rowcount for each table.
====================================
SET NOCOUNT ON
DECLARE @TypTables TABLE (TblName VarChar(80), RecCount Int)
DECLARE @TblName VarChar(80), @RecCount Int,
@SQLStr NVarChar(2000), @ParamStr NVarChar(800)
DECLARE curTypTables CURSOR FAST_FORWARD FOR
SELECT Name FROM SysObjects
WHERE (Name LIKE 'Typ%') AND (XType = 'U')
OPEN curTypTables
FETCH NEXT FROM curTypTables INTO @TblName
WHILE (@@FETCH_STATUS = 0) BEGIN
--PRINT @TblName
SELECT @SQLStr = N'SELECT @RecCount = COUNT(*) FROM ' + @TblName
,@ParamStr = N'@RecCount Int OUTPUT'
EXECUTE SP_ExecuteSQL @SQLStr, @ParamStr, @RecCount = @RecCount OUTPUT
INSERT INTO @TypTables (TblName, RecCount) VALUES (@TblName, ISNULL(@RecCount,0))
FETCH NEXT FROM curTypTables INTO @TblName
END
CLOSE curTypTables
DEALLOCATE curTypTables
SELECT * FROM @TypTables
====================================
Good luck, Jan
If you would find a t-sql script that can compare data in 2 tables and point out differences, please let me know.
October 30, 2007 at 2:08 am
Thanks for your help guys, you've been fantastic!
www.sqlAssociates.co.uk
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply