quote:

---

Originally posted by lazerath

There is a third party utility I've used before from Red Gate called SQL Data Compare (as well as SQL Compare, which is the schema version). The data compare tool will not work very well on very big databases as you might imagine, but if you have a smaller DB it might suit your needs.

I have no connections to Red Gate, just used their products before.

---

quote:

---

Originally posted by lazerath

This this a puzzle or a real world situation? Has it already happened or are you designing a solution to help in the event of a disaster?

---

quote:

---

Originally posted by chadmat

If the original primary is completely offline, there is no way to guarantee that.

-Chad

---

quote:

---

Originally posted by zero1de

I been thereby to provide a solution, which will bee use in test case, to proves that, that the two DBs are equal. This can happen in following way. I create a deduction from prod system, then shut down the system and backup system is online. Now I make a deduction from the backup and compare the two.

THX

quote:

---

Originally posted by lazerath

This this a puzzle or a real world situation? Has it already happened or are you designing a solution to help in the event of a disaster?

---

---

quote:

---

Originally posted by lazerath

quote:

---

Originally posted by zero1de

I been thereby to provide a solution, which will bee use in test case, to proves that, that the two DBs are equal. This can happen in following way. I create a deduction from prod system, then shut down the system and backup system is online. Now I make a deduction from the backup and compare the two.

THX

quote:

---

Originally posted by lazerath

This this a puzzle or a real world situation? Has it already happened or are you designing a solution to help in the event of a disaster?

---

---

It is not clear to me what you mean. What is a deduction? How can you compare the two systems post-deduction if "the prod DB is in time of a disaster point no longer available".

---

quote:

---

Originally posted by chadmat

I don't understand the point? In real life, the disaster will not be determined by you, so if you just want to compare DBs, why simulate a disaster at all? If it is for some sort of DR process, then this test is invalid because you won't know the date/time a disaster will occur.

-Chad

---

quote:

---

Originally posted by lazerath

Ok, so you'll have to do some work and this isn't perfect, but maybe you can do something like this:

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL=COALESCE(@SQL+CONVERT(NVARCHAR(MAX),'
UNION ALL
'),CONVERT(NVARCHAR(MAX),'')) + 'select CHECKSUM_AGG(CHECKSUM(*)) FROM [' + CONVERT(NVARCHAR(MAX),Table_schema) + '].[' + CONVERT(NVARCHAR(MAX),Table_name) + ']'
FROM INFORMATION_SCHEMA.Tables
ORDER BY TABLE_SCHEMA, TABLE_NAME

select @SQL;

EXEC (@SQL);

Keep in mind CHECKSUM won't work on data types: text, ntext, image, XML, cursor, and sql_variant with any one of the preceding types as its base type. If you don't have any of these datatypes, you could do a CHECKSUM_AGG on the result of the UNION ALL. Otherwise, you'll need to find someway to exclude those datatypes.

---

quote:

---

Originally posted by lazerath

It's producing a CHECKSUM of all the data in each row in a table, then a CHECKSUM aggregate of those to produce a CHECKSUM of the table. It's then doing that for every table in the database and UNIONing them together. This list can then have a further CHECKSUM_AGG applied to it in order to get a single integer value for the DB.

A check sum in principal is just a way to take a lot of data and get a semi-unique value that represents it. If the data changes, in theory a new checksum will be generated. I say theory, because this function uses INT and there are many chances for a "collision", which means different data can have the same CHECKSUM. For your purposes, it should be good enough.

Again, there are many deficiencies to this approach, but the general principal should be relevant.

---

quote:

---

Originally posted by lazerath

Like I said, you will need to do some work and it's not perfect, but this shows you a path.

---

quote:

---

Originally posted by zero1de

ok you mean that a should find a way to extract this fields Luke ntext etc. Bit how can i Do that ? can you give me a Exempel


Thx

quote:

---

Originally posted by lazerath

Like I said, you will need to do some work and it's not perfect, but this shows you a path.

---

---

DECLARE @SQL NVARCHAR(MAX);
DECLARE @datatype 
TABLE (
DATA_TYPE NVARCHAR(128),
ALTERNATE_EXPRESSION NVARCHAR(128)
);

INSERT @datatype VALUES('text','CONVERT(VARCHAR(MAX),%n)')
INSERT @datatype VALUES('ntext','CONVERT(NVARCHAR(MAX),%n)')
INSERT @datatype VALUES('image','CONVERT(VARBINARY(MAX),%n)')
INSERT @datatype VALUES('XML','CONVERT(VARCHAR(MAX),%n)')

DECLARE @TABLE
TABLE (
Table_schema sysname,
Table_name sysname,
column_list NVARCHAR(MAX)
PRIMARY KEY(Table_schema, Table_name)
);

DECLARE @COLUMN
TABLE (
ID INT,
Table_schema sysname,
Table_name sysname,
column_name sysname,
ORDINAL_POSITION INT,
data_type NVARCHAR(128),
max_ordinal INT,
column_list NVARCHAR(MAX)
PRIMARY KEY(ID)
);


-- Capture an overridable column_listl, also enforce an order 
-- without using ORDER BY in the following SELECT

INSERT @TABLE
SELECT t.Table_schema, t.Table_name, CASE WHEN c.COLUMN_NAME IS NULL THEN '*' END
FROM INFORMATION_SCHEMA.Tables AS t
OUTER APPLY (
SELECT TOP 1 c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.Table_schema = t.Table_schema AND c.Table_name = t.Table_name
AND c.DATA_TYPE IN (SELECT d.DATA_TYPE FROM @datatype d)
) AS c;

-- need to assemble an ordered list for following update statement to build column_list
INSERT @COLUMN
SELECT	ROW_NUMBER() OVER (ORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION) AS ID,
		c.Table_schema,
		c.Table_name,
		c.column_name,
		c.ORDINAL_POSITION,
		c.data_type,
		MAX(c.ORDINAL_POSITION) OVER(PARTITION BY c.Table_schema, c.Table_name) AS max_ordinal,
		NULL AS column_list
FROM @TABLE AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS c
ON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_name
WHERE t.COLUMN_LIST IS NULL
ORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION;

DECLARE @last_table_schema sysname, @last_table_name sysname, @column_list NVARCHAR(MAX);

-- String concatenation to get COLUMN_LIST built the hard way -- I wish i had a CONCAT_STRING_AGG() function built in
UPDATE	c
SET		@column_list = COALESCE(CASE WHEN COALESCE(@last_table_schema,'') = c.table_schema
						AND COALESCE(@last_table_name,'') = c.table_name
						THEN @column_list + ',' END,'') + REPLACE(COALESCE(d.ALTERNATE_EXPRESSION,'%n'),'%n','[' + c.column_name + ']')
,		@last_table_schema = c.table_schema
,		@last_table_name = c.table_name
,		c.column_list = @column_list
FROM @COLUMN AS c
LEFT JOIN @datatype d ON c.DATA_TYPE = d.DATA_TYPE;

-- go back and update our @TABLE variable with the column list
UPDATE t
SET t.COLUMN_LIST = c.COLUMN_LIST
FROM @TABLE AS t
JOIN @COLUMN AS c
ON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_name
WHERE c.max_ordinal = c.ORDINAL_POSITION -- get only the last column as only it will have the full column list


-- Assemble the CHECKSUM for each row and a CHECKSUM_AGG for each TABLE with a UNION ALL to pull them together
SELECT @SQL=COALESCE(@SQL+CONVERT(NVARCHAR(MAX),' UNION ALL 
'),CONVERT(NVARCHAR(MAX),'')) 
+ 'SELECT CHECKSUM_AGG(CHECKSUM('+ t.column_list + ')) AS CS FROM [' + CONVERT(NVARCHAR(MAX),t.Table_schema) + '].[' + CONVERT(NVARCHAR(MAX),t.Table_name) + ']'
FROM @TABLE t;


-- Include a CHECKSUM_AGG on the resultset so we can get a DB level CHECKSUM
SELECT @SQL = 'SELECT CHECKSUM_AGG(z.CS) AS DB_CHECKSUM FROM (
' + @SQL + ') AS z';

-- Run the Dynamic SQL statement to get a DB checksum
EXEC(@SQL);

quote:

---

Originally posted by lazerath

I was hoping you would do more work and then show us where you were stuck, but I figured I'd just put something together for you. Try this:

DECLARE @SQL NVARCHAR(MAX);
DECLARE @datatype 
TABLE (
DATA_TYPE NVARCHAR(128),
ALTERNATE_EXPRESSION NVARCHAR(128)
);

INSERT @datatype VALUES('text','CONVERT(VARCHAR(MAX),%n)')
INSERT @datatype VALUES('ntext','CONVERT(NVARCHAR(MAX),%n)')
INSERT @datatype VALUES('image','CONVERT(VARBINARY(MAX),%n)')
INSERT @datatype VALUES('XML','CONVERT(VARCHAR(MAX),%n)')

DECLARE @TABLE
TABLE (
Table_schema sysname,
Table_name sysname,
column_list NVARCHAR(MAX)
PRIMARY KEY(Table_schema, Table_name)
);

DECLARE @COLUMN
TABLE (
ID INT,
Table_schema sysname,
Table_name sysname,
column_name sysname,
ORDINAL_POSITION INT,
data_type NVARCHAR(128),
max_ordinal INT,
column_list NVARCHAR(MAX)
PRIMARY KEY(ID)
);


-- Capture an overridable column_listl, also enforce an order 
-- without using ORDER BY in the following SELECT

INSERT @TABLE
SELECT t.Table_schema, t.Table_name, CASE WHEN c.COLUMN_NAME IS NULL THEN '*' END
FROM INFORMATION_SCHEMA.Tables AS t
OUTER APPLY (
SELECT TOP 1 c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.Table_schema = t.Table_schema AND c.Table_name = t.Table_name
AND c.DATA_TYPE IN (SELECT d.DATA_TYPE FROM @datatype d)
) AS c;

-- need to assemble an ordered list for following update statement to build column_list
INSERT @COLUMN
SELECT	ROW_NUMBER() OVER (ORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION) AS ID,
		c.Table_schema,
		c.Table_name,
		c.column_name,
		c.ORDINAL_POSITION,
		c.data_type,
		MAX(c.ORDINAL_POSITION) OVER(PARTITION BY c.Table_schema, c.Table_name) AS max_ordinal,
		NULL AS column_list
FROM @TABLE AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS c
ON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_name
WHERE t.COLUMN_LIST IS NULL
ORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION;

DECLARE @last_table_schema sysname, @last_table_name sysname, @column_list NVARCHAR(MAX);

-- String concatenation to get COLUMN_LIST built the hard way -- I wish i had a CONCAT_STRING_AGG() function built in
UPDATE	c
SET		@column_list = COALESCE(CASE WHEN COALESCE(@last_table_schema,'') = c.table_schema
						AND COALESCE(@last_table_name,'') = c.table_name
						THEN @column_list + ',' END,'') + REPLACE(COALESCE(d.ALTERNATE_EXPRESSION,'%n'),'%n','[' + c.column_name + ']')
,		@last_table_schema = c.table_schema
,		@last_table_name = c.table_name
,		c.column_list = @column_list
FROM @COLUMN AS c
LEFT JOIN @datatype d ON c.DATA_TYPE = d.DATA_TYPE;

-- go back and update our @TABLE variable with the column list
UPDATE t
SET t.COLUMN_LIST = c.COLUMN_LIST
FROM @TABLE AS t
JOIN @COLUMN AS c
ON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_name
WHERE c.max_ordinal = c.ORDINAL_POSITION -- get only the last column as only it will have the full column list


-- Assemble the CHECKSUM for each row and a CHECKSUM_AGG for each TABLE with a UNION ALL to pull them together
SELECT @SQL=COALESCE(@SQL+CONVERT(NVARCHAR(MAX),' UNION ALL 
'),CONVERT(NVARCHAR(MAX),'')) 
+ 'SELECT CHECKSUM_AGG(CHECKSUM('+ t.column_list + ')) AS CS FROM [' + CONVERT(NVARCHAR(MAX),t.Table_schema) + '].[' + CONVERT(NVARCHAR(MAX),t.Table_name) + ']'
FROM @TABLE t;


-- Include a CHECKSUM_AGG on the resultset so we can get a DB level CHECKSUM
SELECT @SQL = 'SELECT CHECKSUM_AGG(z.CS) AS DB_CHECKSUM FROM (
' + @SQL + ') AS z';

-- Run the Dynamic SQL statement to get a DB checksum
EXEC(@SQL);

---