Useful for SQL Server upgrade and migration, instance consistency check in the enterprise, et cetera.
Known assumptions:
1. pyodbc and proper SQL Server database driver setup and tested;
2. Windows Active Directory trusted connection to both instances. Login/password can also be used with very simple modification of the code;
3. Proper permission is set on both instances.
Known limitations:
1. It is based on object name comparison. In many cases, this is “good enough”;
2. It does not compare End Points or Server level triggers, although that should be added easily.
Command line usage:
scriptName -s sourceInstance -t targetInstance
import pyodbc, argparse def getAllRows(cn, sql): cursor = cn.cursor() cursor.execute(sql) return cursor.fetchall() def rowCompare(objectType, rowsSource, rowsTarget): if rowsSource == rowsTarget: print "%s(s) appeared to have been migrated." % objectType else: print "%s(s) not migrated yet:" % objectType for row in rowsSource: if row not in rowsTarget: print row[0] parser = argparse.ArgumentParser(description='SQL Server migration/upgrade comparison: databases') parser.add_argument('-s', '--source-server', help='Source server/instance', required=True, dest='sInstance') parser.add_argument('-t', '--target-server', help='Target server/instance', required=True, dest='dInstance') argList = parser.parse_args() try: cnSource = pyodbc.connect("DRIVER={SQL Server};SERVER=%s;DATABASE=master;Trusted_Connection=yes" % argList.sInstance) cnTarget = pyodbc.connect("DRIVER={SQL Server};SERVER=%s;DATABASE=master;Trusted_Connection=yes" % argList.dInstance) except: print "Couldn't connect to %s. It is down or you might have had a typo." """1. Compare if all databases on the source server are present at the target server""" print "\n" sql = "select name from master.sys.databases order by name" rowsSource = getAllRows(cnSource, sql) rowsTarget = getAllRows(cnTarget, sql) rowCompare('Database', rowsSource, rowsTarget) """2. Compare if all logins on the source server are present at the target server""" print "\n" sql = "select name from master.dbo.syslogins where name not like '##%' order by name" rowsSource = getAllRows(cnSource, sql) rowsTarget = getAllRows(cnTarget, sql) rowCompare('Login', rowsSource, rowsTarget) """3. Compare if all linked servers on the source server are present at the target server""" print "\n" sql = "select name from master.sys.servers where server_id > 0 order by name" rowsSource = getAllRows(cnSource, sql) rowsTarget = getAllRows(cnTarget, sql) rowCompare('Linked server', rowsSource, rowsTarget) """4. Compare if all jobs on the source server are present at the target server""" print "\n" sql = "select name from msdb.dbo.sysjobs order by name" rowsSource = getAllRows(cnSource, sql) rowsTarget = getAllRows(cnTarget, sql) rowCompare('Job', rowsSource, rowsTarget) """5. Compare if all credentials on the source server are present at the target server""" print "\n" sql = "select name from master.sys.credentials order by name" rowsSource = getAllRows(cnSource, sql) rowsTarget = getAllRows(cnTarget, sql) rowCompare('Credential', rowsSource, rowsTarget) """6. Compare if all system stored procedures inside master that start with sp_ on the source server are present at the target server""" print "\n" sql = "select name from master.sys.all_objects where type = 'p' and name like 'sp_%' and is_ms_shipped = 0 order by name" rowsSource = getAllRows(cnSource, sql) rowsTarget = getAllRows(cnTarget, sql) rowCompare('System stored procedure', rowsSource, rowsTarget)
Nice one.
Well written & concise.
Thanks for placing in the Public Domain.
Earlier this year I played around with Jython & Python and this certainly showed how easy it can be to put together a quick working version with this Language.
Daniel