0

I'm working with:
VS2013 Professional, Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

I have kind of a two part question. What I'm wanting to achieve is: I want to, as seamlessly as possible, to be able to work on the same project on my work PC and home PC. As of right now, I am using online hosted Subversion for source control which is working fine for application code. The part I have no control over at the moment is the database. I would like if I could get "all" database changes made at either work or home to synch to my other machine.

By database changes, I mean:

  • Schema Changes
  • Data within specific "Application" tables (I obviously do not intend to synch data in all tables)

I followed this just to test getting a DB schema into my project and under source control: https://msdn.microsoft.com/en-us/library/aa833194%28v=vs.100%29.aspx

It seems to work fine. However, that covers schema changes when working on one machine. If I then go home and want to:

  • either build from new or update changes to the schema on my home machine, or
  • update data in base "Application" tables

...I have no clue how to do that, or if it is even possible?

I would think there should be a simple (ha!) way for making the schema changes flow through easily?

But changes to app tables might be harder - I'm happy to write a sql script to manage that, but I'd like to be able to have that script automatically run when I do a "refresh" my local copy of the database.

tbone
  • 5,715
  • 20
  • 87
  • 134
  • Check out [this answer](http://stackoverflow.com/questions/29282099/how-to-develop-t-sql-in-visual-studio/29285263#29285263) for some ideas. The answer addresses syncing schema and data between multiple developers which is not much different than your situation. – Keith Mar 27 '15 at 12:24

1 Answers1

0

For schema changes, there are good blogs out there on using SSDT/DataDude/VS DB Projects. Jamie Thomson has written quite a few times on his experiences. I've written up my experiences here: http://schottsql.blogspot.com/2013/10/all-ssdt-articles.html

For data - you can use the native "Data Compare" option under the "SQL" menu in SSDT. It's not perfect, but it can help. Overall, though, what you'd want is one of a couple of things: 1. Extract data from the shared system, write a task to populate that - batch files w/ BCP, SSIS, or some apps that can actually generate T-SQL for you. 2. Write it yourself, being sure to guard against attempts to insert duplicate data and ensuring the key values remain unchanged. 3. Buy a copy of Red-Gate's SQL Data Compare Pro. You can save the compare options and can then execute those through the command line.

If you need this for multiple developers, option 1 or 2 is probably the best way to go, though you can use SQL Data Compare to get you started with a pretty good script. You should also be able to use something like Mladen Prajdic's SSMS Tools Pack to script result sets to T-SQL inserts that you could re-use.

If you use one of those options and combine it with a post-deploy script (maybe even one that only runs if this is a "new" build), you should be off to a good start.

Peter Schott
  • 4,521
  • 21
  • 30