Geek Answers Handbook

SQL Server 2005 - Development / Production Database Synchronization

I have a fairly large SQL Server 2005 database, which is under constant development. Each time, so often, I either get a new developer, or need to deploy large-scale schema changes to a production server.

My main problem is deploying schema + data updates on development machines from the "main" development copy.

Are there any built-in functions or tools to publish schema + data in this way? I would like it to take as little time as possible. Can this be done from SSMS?

Thank you in advance for your time.

+4
source share
6 answers

I suggest using RedGate tools (this is not advertising, but real experience with them) that can deploy schema changes and / or data:

  • SQL Compare for schema changes
  • SQL data comparison for data changes

I have used these tools in the past (others also), and they really helped with the development process. These tools are not free, but their price is more than acceptable for any development team.

Check the RedGate website

+7
source

I know this was answered many years ago, but check out http://en.wikipedia.org/wiki/Microsoft_SQL_Server_Compare_Tools

It features a number of free and paid comparison tools.
For example, the free tool listed in SQL Server Compare by Yes Soft list http://www.yessoft.com/software/sqlservercompare/sqlservercompare.htm http://www.yessoft.com/software/sqlservercompare/screenshot1.jpg

Information provided on the site:
-List of free tools
-List of paid tools
-Features: Compare, Generate Script Sync, Sync, Licensing

Growth potential: 5 free tools are indicated, 21 paid tools are listed.
Downside: This is Wikipedia. And the links seem to point to Wikipedia pages, not the actual home pages, but if you find them on Google,

+4
source

I will add my voice to the Red Gates tools - they are not expensive and they are useful - we have several complex databases, and using a tool like SQL Compare is the only way I would be sure to get the update scripts correctly. We used to have some tools, sometimes it was pretty scary

+3
source

You can schedule tasks for backup and recovery using SSMS. In my opinion, this is the simplest solution. The frequency of this task will depend on how dynamic your circuit / data is.

http://msdn.microsoft.com/en-us/library/ms177429.aspx

+2
source

You can always just separate a copy of the database and then re-set the bit. This strategy only works if it’s not a problem for the host to be unavailable for several minutes while the copy is running.

This is obviously just a snapshot of db at that point in time, and if you are looking for a more dynamic solution, you might want to study replication.

+2
source

You can simply back up and restore using standard SQL Server features.
This is what we do. In any case, we back up daily on the production server at 9 pm.

Then at 11:00 we run the SQL Server Agent job on the test server, which restores the last backup from the production server ("RESTORE DATABASE ...").

The only con: you have to get used to the fact that all the changes you make in the test database are disabled the next morning :-)

+2
source

More articles:


All Articles