SQL Server 2005 - Syncing development/production databases

Question

I've got a rather large SQL Server 2005 database that is under constant development. Every so often, I either get a new developer or need to deploy wide-scale schema changes to the production server.

My main concern is deploying schema + data updates to developer machines from the "master" development copy.

Is there some built-in functionality or tools for publishing schema + data in such a fashion? I'd like it to take as little time as possible. Can it be done from within SSMS?

Thanks in advance for your time

Answer 1

You can schedule jobs for back up and restore using SSMS. In my view, this is the simplest solution. The frequency of this job will depend on how dynamic is your schema/data.

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

Answer 2

You could always just detach the database copy it then re-atach the db. This strategy only works if its not an issue for the "master" to be unavailable for a few minutes while the copy is carried out.

This is obviously only a snapshot of the db at that moment in time, and if you're looking for a more dynamic solution you may want to look into replication.

Answer 3

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

• SQL Compare for schema changes
• SQL Data Compare for data changes

I used these tools in the past (others as well) and they really helped with development staging process. These tools aren't free but their price is more than acceptable for any development team.

Check RedGate web site

Answer 4

You could just backup and restore using the standard SQL Server functions.
That's what we do. We run a daily backup on the production server at 9 pm anyway.

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

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

Answer 5

I'll add my vote for Red Gates tools - they're not expensive & they are useful - we have several complex databases & using a tool like SQL Compare is the only way I'd feel confident about getting the update scripts right. Before we had any tools it was pretty scary sometimes

Answer 6

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

It shows a number of free and paid compare tools.
For example a free tool listed is SQL Server Compare by Yes Soft http://www.yessoft.com/software/sqlservercompare/sqlservercompare.htm

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

Upside: 5 free tools are listed, 21 paid tools listed.
Downside: It is wikipedia. And the links seems to point to wikipedia pages instead of the actual homepages but if you google you will find them.