Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Heinz23
Yak Posting Veteran
84 Posts |
 Posted - 2011-07-10 : 05:07:43
|
| Hi all,as developer I have mainly 2 MS SQL Databases: One locally for development, and one live database on the server. What is the best way to keep the structure (SPs, table columns, views, functions, etc. Not Data!) of both in sync? I always have the problem that I'm developing and developing and 1 month later I want to deploy. I know there are sync tools, but my live SQL server is on the internet so I've setup the firewall to block external MS SQL requests. Is there any way to automatically create a script with all changes since a given date? For example on 01.01. both database structures were the same. Now on 28.01. I want to deploy my changes. Now i want to generate a script with all changes since 28.01. Is that possible? Or is there a better way? As said, its' NOT about the data, only the structure.many thanks! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-10 : 06:12:21
|
| Look at RedGate's SQLCompare. There's no built-in way with SQL Server. Either you manually keep track of them, spend huge amounts of time writing and testing your own sync tool, or buy a 3rd party product to do it for you.--Gail ShawSQL Server MVP |
 |
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2011-07-10 : 07:22:22
|
| Their website is currently down, but Google Cache says that RedGate's SQLCompare compares 2 databases. As I don't have direct access to the live database this would not work for me. Additionally I'm a home developer so I would like to avoid spending lots of money. I've also found [url]http://xsqlsoftware.com/Product/Sql_Data_Compare.aspx[/url] which is free for SQL Express but also needs access to both databases for comparison.I wonder whether SQL Server Express 2008 stores the last modification date of procedures, table structures, views etc. so it would be possible to get a list of latest modifications? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-10 : 07:51:34
|
| It can also compare a backup and a database, so you can copy a backup of live and generate your change script that way.It's a couple hundred dollars for a single licence, hardly large amounts of money. Probably cheaper than doing it yourself (compare hourly rate to total amount)There's a modify date for all objects, but it says nothing about what was modified. Views, procs, functions you can just copy the newer version, tables are far from as easy.--Gail ShawSQL Server MVP |
|
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2011-07-10 : 07:54:24
|
| As GilaMonster said there is no built-in way so I'm now using OpenDBDiff [url]http://opendbiff.codeplex.com/[/url]. It's open source. I have a daily backup of my live database. When I now want to sync the structure, I restore the latest daily live backup into my developer SQL Server with a different name (e.g. 'zDatabase') and then run OpenDBDiff to compare live and developer SQL Database. OpenDBDiff returns a query which I could start on the live server and all updates are processed. Thanks GilaMonster for pointing me to the right direction! |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
|
|
|
|
|
|
|
Sync MS SQL Database Structure?