2

I have a database in SQL server on local computer, we have the same database on remote p.c , i want to sync it with remote server, is there any script for SQL server or any plugin? Thank you.

OS: XP-PROFESSIONAL SP3 DATABASE : SQLSERVER 2008 EXPRESS

I want to synchronize the whole schema. And remote will be always correct.

Great Majestics
  • 129
  • Can you give us more information to assist you better. What is the OS environment & the database environment (whether its MSSQL or MySQL) you're using.. This will help us to advise you accordingly. :) – AzkerM Sep 15 '13 at 16:14
  • Are you looking to synchronize the schema, data, or schema + data across those environments? How does the data change? Do you only add rows or is data changed or deleted between systems? In the case of a conflict (local says B, remote says C), which one is correct? – billinkc Sep 18 '13 at 23:13

2 Answers2

1

Writing update scripts that will synchronize two databases manually is quite a risky job. You can maybe try to compare these by creating scripts for all objects and then use a file compare tool to find the deltas between the two schemas and change it. But for even a bit larger databases I would not recommend this method since it’s quite easy to miss some change

using some third-party tool is probably the must here, so you can try ApexSQL Diff or free MS SSDT

ApexSQL Diff can use database backups, script folders, source controls, and proprietary snapshots as data sources. It supports automation via CLI

Disclaimer: I'm working for ApexSQL as a Technical Support Engineer

Glorfindel
  • 4,099
Nikola Dimitrijevic
  • 4,155
0

There are two approaches. Either manually moving the structures/data in a one-time-effort or using automatically using Replication for continuous updates/synching.

For the simple manual approach, Save each table structure to a SQL file. Right click on the table, "Script table as" -> "Create To" -> file. Then on the DB itself, right click -> "Tasks" -> "Export Data" -> [If the remote db is accessible, you can move it here, if not, save to a file and import it on the other machine]

For the Automatic approach, each SQL Server is configured for Replication. The options vary widely. A good starting point is the Chapter on Replication from the MS SQL 2008 Manual.

jdh
  • 7,083
  • how to do replication on sql express , since express edition cannot act as publisher ,so what is the alternate in case we have express edition in both the servers. – raz Sep 07 '17 at 18:40