How to copy SQL Server database from one machine to another(Copy DB from Instance to Another Instance )

Method 1: SQL Server has feature which copy database from one database to another database and it can be automated as well using SSIS.

Make sure you have SQL Server Agent Turned on as this feature will create a job. The same job will execute the task. Make sure that SSIS is properly configured as well with necessary security permissions. You can automate this process as well control error logging.

Following are the steps to copy database from one instance to another instance.

Specify Source Server

Specify Destination Server

Here you can select option if you want to keep the database ONLINE when it is being copied.

You can also select option of MOVE or COPY database as well.

Give appropriate database name.

On this screen you can select additional options to copy as well.

You create the package over here.

You can schedule the package using SQL Server Agent.

When this process is over it will show the success message and database will be copied to another server.

You can see how easy is the process to copy the database to another server.Watch SQL in Sixty Seconds Episode on same subject.

Method 2:How to manually copy a SQL Server database from one machine to another

Step by step instruction for manually copying a SQL Server database to a separate instance of SQL Server. This procedure applies to both MOVEit DMZ and MOVEit Central databases (examples below use MOVEit Central's "micstats" database with a SQL Server login/database user called "moveitcentral").


1.  Backup the current database. First, stop the services for the MOVEit product(s) using this database. To perform the backup, connect to SQL Server with using either SQL Server Management Studio or by opening a command prompt and running "sqlcmd.exe".  If using sqlcmd.exe, use the following command to connect to SQL Server (if connecting to SQLExpress you must specify this instance):

sqlcmd -S hostname[\SQLExpress] -U username

Issue the following query to backup the MOVEit database in question (our example uses "micstats" for the database name, here you should substitute the name of your database. You can also choose a different location to save the backup file.):

BACKUP DATABASE micstats
TO DISK = 'C:\tmp\micstats.bak'
WITH FORMAT,
NAME = 'Full Backup of micstats'
GO

2.  Copy the "micstats.bak" file to the new server.

3.  Restore the backup file on the new server. Stop the services for all MOVEit products using the destination database. The SQL Server service account must have Read access to the backup file. Connect to SQL server, either using Management Studio or sqlcmd.exe, and submit the following query from the master database (or any database other than the destination database).

Note this will overwrite all existing data if micstats already exists:

RESTORE DATABASE micstats
FROM DISK = 'C:\tmp\micstats.bak'
WITH RECOVERY, REPLACE
GO


4.  Next, check to make sure that a valid SQL Server username and login name exist. During the Restore process, any previous usernames tied to the MOVEit database will lose their Login Name associations, which means MOVEit will be unable to connect to the new database after a Restore. First, make sure that the Login Name that MOVEit is using to connect to SQL Server exists in the destination SQL Server instance (this should already exist if MOVEit has been installed). If using SQL Server Management Studio, you can verify this by looking under "Security - Logins".

If using sqlcmd.exe, use the following query to list all SQL Server logins, you should see the MOVEit login listed here:

SELECT name FROM sys.server_principals
GO

5.  Once you verified and noted the name of the MOVEit Login, either create a new database user for the newly restored database and associate this with the MOVEit Login, or, re-associate the existing MOVEit database user with this login (if the SQL user was created using the MOVEit installation program, then you can assume the SQL server login and database user share the same name). The latter is most easily accomplished by submitting a simple query (substituing appropriate username/login):

ALTER USER moveitcentral WITH LOGIN=moveitcentral
GO

Alternativey, If creating a new database user, in addition to associating the user with the MOVEit login, also make sure to give the user "db_datareader" and "db_datawriter" permissions. Creating new user is most easily accomplish using the SQL Server Management Studio GUI. To add a new user, go to "Security - Users" underneath the database in question, right-click, and choose "New User...":





5.  Verify the database connection is working by either starting the MOVEit services or by testing the database connection through MOVEit's Config utility.

6. If you are running MOVEit DMZ, run the following command on the new SQL Server:

sp_configure 'clr enabled', 1;

To make the change take effect, you will have to run the following statement:

RECONFIGURE

However, according to Microsoft:

"WOW64 servers must be restarted before the changes to this setting will take effect. Restart is not required for other server types."

A WOW64 server would be a 32-bit version of SQL Server running on a 64-bit operating system.