“Copy database” operation, quick ‘n easy way to make duplicate database

I was trying to create a demo for one of my experiment, and I had to make create duplicate database for that purpose because I didn’t wanted to do tests on my TestDB Winking smile so quickest option was to use “Copy Database” feature in SSMS, because other option like restoring backup of DB with another name is kind of too much of work since this DB is just for testing I don’t have any backups of it.

So I choose this COPY DATABASE option, which is available in all SQL Server editions (I guess except Express edition). This is actually very easy step by step process.

Copy Database option can be accessed by right click on database that you want to copy then Tasks –>Copy Database.

Though it looks redundant, it will ask again for information about Source Database. Simply select your source database. Then it will ask for information about Destination Database. In this case both source and destination are same.

Copy_DB Copy_DB_8 Copy_DB_9

Next step will be to select transfer method. You can either select “Detach\Attach Method” which will actually detach database copy it to destination server and then attach it back to both source and destination servers. (Be warned: As this method detaches database, DB itself will not be available during whole process)

Copy_DB_2 Copy_DB_3

Or use SMO method (more programmatic approach) which will keep database available even during this copy process. For this demo, I’ve used Detach/Attach method.

Once this method selected, you will see options about which databases you want to MOVE or COPY !! Yes, with this option you can actually MOVE your database as well !! Funny thing is, you will not know that this option exists if you have never used this wizard. Since I wanted to copy only one DB, I just selected one database from list. And I think this is also redundant option since I’ve already right clicked on DB and selected “Copy Database”, which already indicates that I want to copy selected database, isn’t it ??

Next step is to configure details about new database, e.g. DB name, file name, location, size etc. Here you will see two extra option to configure in event of DB with same name exits on destination server. You can either drop existing database or terminate copy database process.

Copy_DB_4 Copy_DB_5

Even though you see this as a wizard, in background it is simply SSIS package based on your inputs. In fact, upon creation this package is deployed to db. So if you log into integration services of server you will see this package is there in the end. In this step, user can actually change name for package and select logging option for execution of this package.

In next step, user can setup schedule for package. I wanted to run it immediately so I choose “Run Immediately”. Alternatively, you can select “Schedule” to create a schedule run for this task.

Copy_DB_6 Copy_DB_7

Final screen presents all settings we selected throughout  the wizard, once user click on FINISH button, wizard ends and it creates new package to transfer database and deploys it to DB Server.


I think it is really nice feature for quick database transfer. But if you need to move logins and other jobs, then you need to transfer more than one DBs (Master for logins and MSDB for jobs)

That’s it for now…

It’s Just A Thought … Peace

Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *