Tag Archives: Copy Database

Copy Database wizard in SQL Server 2012

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.

1OpenCopyDatabaseWizard
Once you get “Copy Database” wizard in SQL Server, click on “Next” button from the following dialog box.

2ClickNext

 

Select the source SQL Server Instance in “Source Server” text box and give proper credential “Windows Authentication” or “SQL Server Authentication”
3SourceServer

 

Select the destination (where you want to copy database) SQL Server Instance in “Destination Server” text box and give proper credential “Windows Authentication” or “SQL Server Authentication”
4DestinationServer

 

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.
5SMOorAttachDetach

 

You have to select database(s) which you want to copy. You can even use “Move” checkbox if you don’t want database in source server and want to move database to another server completely.
6DatabaseToCopyorMove

 

Here you have to give new name of database for destination server. If you are having different SQL Server Instance then source, you can have even same name of database, no need to give new name.
7FileNameForNewdatabase

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.
8PackageName

 

You can run that SSIS package immediately to copy database one time or schedule it if you want copy of your database at some other server periodically, you can mention the schedule also.
9JobForDatabaseMove

 

Have a look at final summary of your selection so far and click on “Finish” button.
10ClickOnFinish

 

If you get “Success” status for all steps, you are done.
11ClickOnClose

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.