SQL Source Control 3

There are two common ways for teams to develop databases:

Dedicated
Each developer has their own copy of the database

Shared
Developers share a single copy of the database

Some development teams use combinations of the two. For example, a large team might use more than one shared development database.

Dedicated development

Each developer works on their own "sandbox" copy of the database. The copy might be local or on a central server.

The developer makes changes independently, tests them, and commits them to source control once they're confident in the changes. After the changes are committed, other developers can apply them to their copy of the database. This mirrors application development.

Because each developer is working in an isolated environment, there's no risk of accidentally overwriting someone else's changes. You're free to develop complex changes that might cause other parts of the database or the application to break. If something goes wrong, it doesn't affect other developers.

Shared development

Each developer works on a single shared copy of the database. This is usually created from a backup of the production database. When a milestone is reached, a deployment script is created to apply the final state of the database to a test database.

Because the changes are made directly to the database itself, you don't need to use the Get latest tab. There are no changes to share or get, and the database always has the most recent changes.

However, there's no safe way to test changes in isolation, and developers may overwrite each others' changes by mistake. Additionally, information about who made changes is sometimes lost.

Setting up SQL Source Control for dedicated development

One developer links their copy of the database to a location in source control for the first time.
The developer commits the objects.
The other developers link their own copy of the database to that source control location.
Alternatively, they can create a new blank database and link it to the database in source control, or restore a backup of the development database. Restoring a backup has the advantage of including any necessary data.
Each developer gets the latest version of the database.

Setting up SQL Source Control for shared development

One developer links the shared database to a location in source control for the first time.
The developer commits the objects.
In SQL Server Management Studio, the other developers connect to the database and link to source control.

For instructions, see Linking to source control.

Switching to dedicated development

To switch from shared to dedicated development, each developer on your team needs to:

Unlink the database from source control.
Create an empty database. This will be the developer's dedicated copy.
For easy identification, you should name the database something similar to database you're about to copy.
Link the new database to the source control repository that the shared database was linked to.
Go to the Get latest tab and get the changes.
The new database is updated with the changes from the original database, creating a dedicated copy.