Using Sql sever management studio 2012 (SSMS 2012) we can move one or more databases from one server to another or same server.
Steps for this are:
Step 1: Right click on your database and choose Copy Database...
Step 2: We may get welcome window. Click on Next button.
Step 3: In Select a Source Server window, choose your source database server name from where you want to move or copy databases and its authentication. Click on Next button.
Step 4: In Select a Destination Server window, choose your destination database server where you want to move or copy your databases and its authentication. Click on Next button.
Note: It is necessary to enable sql sever agent job at destination server. If it is not enabled you may get a pop up window to enable or run sql server agent job. Click on Yes button.
Step 5: There are two transfer methods.
1. Detach and attach method
2. Sql management object method
Choose any one according to your requirement. Click on Next button.
Step 6: Select databases which you want to move (delete from source sever) or copy. We cannot move or copy databases like system databases (master, tempdb, model and msdb), databases marked as replication etc. Click on Next button.
Step 7: In Configure Destination database window:
Destination database name: You can change the destination database name.
Also choose the option if destination database already present at destination server. Click on Next button.
Step 8: In Configure the Package window if you want change the Package name etc. you can change here, otherwise click on Next button.
Step 9: We can run only one time or schedule the copy or move database process after a regular interval.
To run only one time choose Run immediately.
To schedule it choose Schedule.
Step 10: If you will choose schedule option you will get New Job Schedule window. Fill the scheduling information and click on OK button.
Step 11: In Complete wizard click on Finish button.