How to manually sync MS SQL databases on two different servers
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
BACKUP DATABASE sync_db
TO DISK = 'c:\tmp\sync_db.bak'
MEDIANAME = 'SQLServerBackups',
NAME = 'Full Backup of sync_db';
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
Make sure to check the option close all existing connections and click OK.
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…
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…
You’ll be prompted with a restore DB window. Select device for backup source and locate the backup you just copied.
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.
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.