Quickly clone a database in SQL Server Management Studio

Posted on

Question :

For testing purposes, I often have to clone databases (onto the same server). Currently I do this by creating a backup and restoring it as a new database.

I would love to have such a functionality available as a simple right-click in SQL Server Management Studio (SSMS): right-click a database, choose “Clone” and be done.

Unfortunately I found no such or a similar function in SSMS.

My question:

Does such a “quick clone” function exist or could I implement one by myself (or maybe through some add-in?)

Update 1:

I’ve found a SO question and answer on using the Copy wizard.

This looks promising but takes a lot of wizard steps. Maybe it is possible to do this whole thing with a single click?

Answer :

I just took a look at my ssms. Copy is in SSMS 2012 and 2014 under tasks. I haven’t tried it but it is there. What ssms are you using?

SSMS 64 bit: http://download.microsoft.com/download/5/2/9/529FEF7B-2EFB-439E-A2D1-A1533227CD69/SQLManagementStudio_x64_ENU.exe

What about an ssis package? Create an SP that calls an ssis package which copies (clones) a db given two parameters; source db and destination db.

Leave a Reply

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