How to manually sync MS SQL databases on two different servers

Photo by Florian Krumm on Unsplash

Many times we have a requirement where we want databases on two different servers to have same data, procs, etc.

For example, we want that the data in our stage environment should be same as our dev environment so that we can work on that data without much worry for the environment.

In such scenarios, either we can have a separate stored procedure to do so or we can do this manually using SSMS.

Here we will see how to do it manually using SSMS. For the sake of reference and convenience I’ll assume that we are bringing stage DB’s data to dev DB

Creating A Backup Of Stage DB

Create a full backup of the stage database using the following command

USE sync_db;
GO
BACKUP DATABASE sync_db
TO DISK = 'c:\tmp\sync_db.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of sync_db';
GO

Copy The Backup To Dev Environment

Next you need to copy the database created above to the dev server. The approach for this can vary depending on how your infrastructure is designed.

Two most simple ways that I can think of are

• Having the servers on same network and sharing files within that network
• If this is on AWS you can upload the backup to S3 (you can use corresponding services in GCP and Azure as well) and then download it on dev server

Replace Existing DB With The Backup

Once we have our backup in place on our dev server, we need to replace the existing DB with it.

Many a times we have ongoing connections to DB and log file locks that can cause issues with restoring the database from a backup.

Let us assume that we are doing this activity for a DB named sync_db

Open SSMS and connect to the SQL server for dev.

Once connected, expand the database option from the left navigation pane

Now right click on sync_db > Delete

Delete the existing database

Make sure to check the option close all existing connections and click OK.

Close existing connections

Now that the existing DB is gone, most of our problems are solved. We still however need to have a DB to restore it to.

Create a new database named sync_db. To do so right click databases from the left navigation pane and click New Database…

Create a new database

Enter the name of DB as sync_db and click ok

Then click on databases from the left navigation pane on SSMS and click on Restore Database…

Restore database

You’ll be prompted with a restore DB window. Select device for backup source and locate the backup you just copied.

select Device as backup source

Enter the name of destination database as sync_db_new

Click on options in the left navigation of the prompt window and uncheck tail logs.

Uncheck tail-Log backup

Click OK and wait for restore to complete.

After the process is done you’ll have a new database sync_db_new.

Now delete the existing database sync_db and rename sync_db_new to sync_db.

Rename sync_db_new to sync_db