Efficient Way Of Copying Database In SQL Server


Sometimes when working in Database, we may need to create another working copy of the current database or production database to check our new logic which should not affect the existing objects such as tables, stored procedures or etc. Example, suppose we may need to check few things using the Production database, but applying the changes in production database is not. So in this case, we usually copy the production database and create as a new database to apply our new logics. This method is absolutely fine. But this article we will see how to copy some databases in an efficient way.

Copying Database in SSMS

First thing we have to make sure is our SQL Server Agent is running. If it is not running, then right click the SQL Server Agent and click Start.

Fig. 1 Running SQL Server Agent

Choose the database that we need to take the backup.

Right click the database, navigate to Task, then choose Copy Database as shown below.

Fig. 2 Choose Copy Database from the Tasks

Click next and at one point you can see two options to transfer the data as shown below

Fig. 3 Options to Transfer Data

We have to choose the second option as SQL Management Object Method. Then click next until we reach the below screen

Fig. 4 Entering the Database name

Click next as option for couple of windows and finally we reach the last window and click Finish.

Fig. 5 The Final stage
Note: This process is bit time consuming though sometimes. Depending upon the data.
Fig. 6 The data transfer is happening

Once the above windows process is done, then click the Close button and just refresh the database folder. Our newly copied database is created.

Fig. 7 New Copied Database


In this article we discussed about taking copy of working database in SQL Server in an Efficient way. I hope you all found this tips pretty useful. We will learn more tips and tricks in our upcoming articles. Please leave your feedbacks in the comment section.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: