December 24, 2010 at 7:49 am
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
December 27, 2010 at 9:54 am
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
December 28, 2010 at 8:09 am
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.
December 28, 2010 at 8:13 am
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.
December 28, 2010 at 8:17 am
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.
December 28, 2010 at 11:32 am
And what about try "Transfer database task" in SQL Server integration services to import remote databases and then you can do some comparations?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply