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 
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.
 |
 |
 |
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)
 |
 |
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.
 |
 |
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.
 |
 |
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 … 
