Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Schema validation Expand / Collapse
Author
Message
Posted Friday, December 24, 2010 7:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:46 PM
Points: 116, Visits: 529
Our company has over 5000 sql server instances spread out over the country. The servers are connected to our corporate central data center via a high latency satelite connection. I'm looking for a way to export a our applications schema (tables,constraints,data types..etc) to some type of flat file result (xml,text,....etc) that can be easily transmitted via our network to a central server, imported in to a database, and compared against a "golden" copy of the application database schema. Anybody have any advice or scripts that will export a database schema to a flat file? Thanks

Dustin W. Jones - Database Tech.
Post #1039116
Posted Monday, December 27, 2010 9:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 21, 2013 8:35 AM
Points: 177, Visits: 355
Do you want to do the compare inside or outside of SQL Server? That is do you want a file you would import into the remote SQL server (perhaps as a new DB) and do the compare within SQL Server or do you want to export the local information to a flat file and do a flat file compare?

Also what is the amount of data (not schema) that you need to compare (in the KB, MB or GB?)?

You might want to take a look at SQL Compare from Red Gate
Post #1039472
Posted Tuesday, December 28, 2010 8:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:46 PM
Points: 116, Visits: 529
Do you want to do the compare inside or outside of SQL Server?
That is do you want a file you would import into the remote SQL server (perhaps as a new DB) and do the compare within SQL Server or do you want to export the local information to a flat file and do a flat file compare?

I suppose either way would work. I would like to export the schema from the remote servers to a flat file to the local file system. I could then write a process that resides on the Central master server to collect these flat files from each remote server, import them into a table on the central master server, and then then compare the schema against a master "golden copy". There is no need to compare data, just schema.

I do not think SQL compare will work for us.



Dustin W. Jones - Database Tech.
Post #1039786
Posted Tuesday, December 28, 2010 8:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 21, 2013 8:35 AM
Points: 177, Visits: 355
Well then you could look at doing a select on sysobjects, sysindexes and syscolumns that saves those results to a flat file and then compare that to your 'master copy' of each of those files.
Post #1039791
Posted Tuesday, December 28, 2010 8:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:46 PM
Points: 116, Visits: 529
I was thinking about something along those lines, I guess I was hoping somebody already had something they would could share that I could use a starting point or maybe not reinvent something somebody else already invented.

Dustin W. Jones - Database Tech.
Post #1039799
Posted Tuesday, December 28, 2010 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:49 AM
Points: 28, Visits: 416
And what about try "Transfer database task" in SQL Server integration services to import remote databases and then you can do some comparations?
Post #1039908
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse