April 13, 2009 at 12:10 pm
I do have multiple DEv and Prod servers where in most of the time databases are similar when comapred ot Dev and Prod. Usually Dev databases are moved to Prod periodically as and when updated.
I would like to compare counts on the databases from Dev and Prod each time and make a report before and after moving to prod, how would i automate such process for auditing instead of manually doing everytime and also would like to have an ssrs report with it.
Have you guys did such things before?? i need some input on how to proceed, if you have already did so, can you please share scripts related to this.
thanks
April 13, 2009 at 12:20 pm
What purpose is this report going to serve?
April 13, 2009 at 12:36 pm
we have to do an auditing before and after updating production databases.
April 13, 2009 at 12:40 pm
April 13, 2009 at 12:55 pm
Why not write the output of the scripts to a file and review after implementation. The output could include both DDL and DML.
You could have timestamp appended to the output file to ensure compliance.
April 13, 2009 at 8:53 pm
Hi,
You can create a before and after dump of the objects in the dat base and compare what has changed.
See below.
or SQLDiff does the job pretty well but I am too cheap to buy it.
Stored Procs and functions
Select Routine_Name,Routine_Type,Routine_Definition
Into SPBefore
From
INFORMATION_SCHEMA.ROUTINES
Select Routine_Name,Routine_Type,Routine_Definition
Into SPAfter
From
INFORMATION_SCHEMA.ROUTINES
--View changes
Select B.*, A.*
From Before B
Inner Join After A
On(A.Routine_Name= B.Routine_Name
And A.Routine_Type= B.Routine_Type)
Tables
--Before
Select t.name As TableName,c.Name as ColumnName,
d.name As DataType, c.length As Size
Into Before
From sys.tables as t inner join
sys.syscolumns as c on (t.object_id = c.id)
Inner Join sys.types d on(d.user_type_id = c.xusertype)
Order By t.Name,c.Name
--After
Select t.name As TableName,c.Name as ColumnName,
d.name As DataType, c.length As Size
Into After
From sys.tables as t inner join
sys.syscolumns as c on (t.object_id = c.id)
Inner Join sys.types d on(d.user_type_id = c.xusertype)
Where t.name ! ='Before'
Order By t.Name,c.Name
--View changes
Select B.*, A.*
From Before B
Inner Join After A
On(A.TableName = B.TableName
And A.ColumnName = B.ColumnName)
Cheers
Jamie
April 13, 2009 at 9:08 pm
Now i understand what is "counts"...
Well in my case, when i have to get information in various sql server servers and databases, i use powershell + SMO + XML to do this. In almost cases with 10, 15 code lines i get all information that i need in all servers and databases at once.
But in your case, depending on the complexity of the structure of database and what you want to compare after and before, i think a tool to do this can be used , like jamie said.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply