November 6, 2014 at 9:37 pm
Hi,
I am looking for a script which will help me to capture the object details like constraints, clustered & non-clustered indexes, primary & foreign keys, etc before migration & also after migration and give me the differences after comparison if any.
Since this is on production environment, we do not have approval to use any comparison tool. Being a DBA, I am very poor in TSQL programming. Please help me with a script which can compare the differences before & after migration.
Regards,
SQL DBA
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
November 6, 2014 at 10:30 pm
SQL DBA-909494 (11/6/2014)
Hi,I am looking for a script which will help me to capture the object details like constraints, clustered & non-clustered indexes, primary & foreign keys, etc before migration & also after migration and give me the differences after comparison if any.
Since this is on production environment, we do not have approval to use any comparison tool. Being a DBA, I am very poor in TSQL programming. Please help me with a script which can compare the differences before & after migration.
Regards,
SQL DBA
First a quick question, what exactly are the restrictions on the production environment other than not using comparison tools? Reason for asking is that it isn't really smart trying to re-invent the wheel in a less capable language when there are perfectly good wheels out there.
This can more or less be achieved in T-SQL but that wouldn't be the first choice, I've used SSIS and SMO/DMO (DMO is depreciated) and PowerShell in the rear circumstances when off the shelf tools could not be used.
😎
November 6, 2014 at 10:36 pm
We cannot use powershell too. It would be good to have a script in TSQL itself.
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
November 7, 2014 at 1:16 am
Personally I don't see the issue with allowing scripting tools to access a production server, especially if its for validation purposes.
Assuming the database isn't huge why not just back up and restore to a server where you can use the scripting tools?
I would suspect that if you arnt allowed to use scripting tools on the production server, you also wont be allowed access to the sys metadata tables/views which is where you would get all of the information you need from in order to do a comparison.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 7, 2014 at 1:46 am
I'm not affiliated with RedGate. Have you thought of using SQL Compare[/url]?
It's pretty quick and will show you in a very simple UI any differences between the schema.
November 7, 2014 at 2:33 am
Cadavre (11/7/2014)
I'm not affiliated with RedGate. Have you thought of using SQL Compare[/url]?It's pretty quick and will show you in a very simple UI any differences between the schema.
Unfortunately one of the issues is that he is not permitted to use these types of tools on a production environment, which in my view is asinine.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 7, 2014 at 3:51 am
I am looking for the script as there is a security concern of running any tool except TSQL script. Could you please help me getting it.
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
November 7, 2014 at 6:00 am
SQL DBA-909494 (11/7/2014)
I am looking for the script as there is a security concern of running any tool except TSQL script. Could you please help me getting it.
as I've said previously, I don't see why a professional DB compare tool cant be used against a prod DB, especially something like the REDGATE offering.
I cant give you a script but I will point you in the right direction as I don't have the scripts I usually use to hand at the moment, due to them being on a portable drive sat in the flat.
However to get index information sys.Indexes and join to sys.Index_columns tables
For defaults and check constraints you need to use the sys.default_constraints and sys.check_constraints
Foreign Keys are more complex, as the entry point isn't easy to guess, and from memory you use sys.sysforeignkeys, sys.foreign_keys, sys.foreign_key_columns, sys.columns.
Hope this gives you some pointers.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply