Export (0) Print
Expand All

How to: Synchronize Database Schemas

After you compare the schemas of the source and the target, you can synchronize an entire schema or only the database objects that you specify within that schema. For more information, see Overview of Comparing Database Schemas.

NoteNote

The databases that you compare are known as the source and the target. When you synchronize database schemas, you update the target and leave the source unchanged.

To update the target schema

  1. Compare two schemas. For more information, see How to: Compare the Schemas of Two Databases.

    After the comparison finishes, a table in the Schema Compare window lists the database objects that were compared. Each row represents one database object. For more information, see Overview of Comparing Database Schemas.

  2. (Optional) In the Update Action column, keep or change the synchronization action that appears for each object in the results table.

    Individual synchronization actions are not performed immediately but stored for batch execution in the following step.

    NoteNote

    To reset the Update Action column for all objects of a particular type, right-click the node for that type (for example, the Tables node), and click Restore Defaults.

  3. To synchronize database objects that are different, missing, or new, do one of the following:

    • To update the target immediately, click Write Updates.

      Except for objects marked as Skip, this choice applies the schema of the selected database objects in the source on the corresponding objects in the target. This synchronization means updating, creating, or dropping the target object.

      NoteImportant

      While the schema is being updated, you can cancel the operation by clicking Stop Writing to Target. If you stop the update, no changes are propagated for most object types. However, partial changes to User and Role objects might not get rolled back because these objects cannot be wrapped in transactions.

    • To review changes before updating the target, use the Schema Update Script window or click Export to Editor.

      This choice generates Transact-SQL (T-SQL) script and saves it as a file, which you can review before you run it against the target. This choice also opens a T-SQL editor window that displays the generated script. The generated T-SQL script matches the script that would be run if you clicked Write Updates. You might also take this approach if another person will update the target and you want to give that person a script.

      NoteNote

      Team Edition for Database Professionals does not automatically refresh the results that appear in the Schema Compare window after the Write Updates action finishes. Instead, the status bar is updated to suggest that you click Refresh. This behavior gives you the choice to compare schemas again, an action that could take a long time if the schemas are large.

See Also

Show:
© 2014 Microsoft