Efficient Way Of Copying Database In SQL Server

Introduction

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.

Choose the database that we need to take the backup.

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

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

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

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

Note: This process is bit time consuming though sometimes. Depending upon the data.

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

Conclusion

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.