Schema validation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Schema validation

Schema validation

Rate Topic

Display Mode

Topic Options

Author

Message

Dustin W. Jones

Posted Friday, December 24, 2010 7:49 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:48 PM
Points: 106, Visits: 459

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

Henry Treftz

Posted Monday, December 27, 2010 9:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:51 PM
Points: 175, Visits: 327

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

Dustin W. Jones

Posted Tuesday, December 28, 2010 8:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:48 PM
Points: 106, Visits: 459

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

Henry Treftz

Posted Tuesday, December 28, 2010 8:13 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:51 PM
Points: 175, Visits: 327

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

Dustin W. Jones

Posted Tuesday, December 28, 2010 8:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:48 PM
Points: 106, Visits: 459

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

nekonecny

Posted Tuesday, December 28, 2010 11:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 03, 2013 7:12 AM
Points: 27, Visits: 363

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 »

Permissions