Copy Database wizard in SQL Server 2012
It is observed so many times that people used to use backup of database to restore in another instance or in the same instance with different database name. Few people used to detach database, copy .MDF / .NDF / .LDF file to another instance and attach it back in old and new instance also. I would prefer to use “Copy Database” wizard if I have good network between both instance. If it is a case of instance in same computer, I would definitely use “Copy Database” wizard.
This is not a new facility but this is one of the useful yet under appreciated facility. I am going to copy “AdventureWorks2012” database in the same instance with name “AdventureWorks2012_New”. Please follow the screen capture given below for more details.
Note: Please keep SQL Server Agent running as it will be needed to run “Copy Database” wizard smoothly.
Now, you have two choices here. Either go for first radio button which will detach database (database will be offline and won’t be used), copy it to destination server and Attach it in new server. If you wish to keep database online and available to use, go for second radio button which will use SMO method. This method will work slow as compare with first option but your database will remain usable during copy process.
SQL Server internally create SSIS package and deploy it to SQL Server and call that SSIS package from SQL Server JOB. This Job run either one time or at the scheduled time you specify. You can see package name and location in following dialog box, you don’t need to do anything.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.