[Webinar] Streamline your web hosting managementRegister Today


SQL 2005 Database Sync

Posted on 2010-01-11
Medium Priority
Last Modified: 2012-05-08
I have a w2k3 sql 2005 cluster that is a production database, I want to create another exact copy on another independant w2k3 server so we can test database changes. I want to be able to control the syncing of the dbs. Is there a way to do this inside sql 2005?
Question by:jasonpiper01
  • 2
LVL 26

Expert Comment

ID: 26288656
you can use the replication mechanizms provided by sql server. There different methods such as mirroring, log shipping, backup/restore,.. You should choose the proper one for you and implement that. The criteria for this is the how often do you need to sychronize? can the db go offline while sychronizing, etc..
LVL 15

Expert Comment

by:Faiga Diegel
ID: 26288675
You will need a tool such as Red Gate SQL Compare or any other tool to compare.

Or whatever script you ran (I'm expecting you will run script for schema changes/new/modified objects) in your pre-production DB, you will have to ran the same in production DB.

Author Comment

ID: 26292958
It can be a snap shot of the database, it doesnt need to be live, but the syncing process can not stop the prod database. We are trying to have an updated copy of the database on our qa box so we can teset schema changes and create or modify objects. Ideally we would like to be a on demand feature, so when we are ready to test new additions we can sync the qa, up load the changes and test fully before deploying to production. Does that help?
LVL 15

Accepted Solution

Faiga Diegel earned 2000 total points
ID: 26309732
Okay so you have probably 3 environments -- Your development box then to QA, then to production. I am assuming if there are changes, it will be from development box (unit testing here) then you push it to QA (unit/system testing here) then if everything is approve push to your production box. Right? If you took a copy of your production schema to your dev box (as a starting point) and do changes here, you need a tool to compare the changes from your dev box to the next level (QA) . Using a tool you will be able to generate  changes scripts that will allow you to apply those changes to QA. Then right after testing QA, use that tool again to compare changes from QA to Production (or as how we do it, use the same script that we ran in QA for Production). In this way, snyc up will be easy.

Is this what you need?

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick step-by-step overview of installing and configuring Carbonite Server Backup.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question