How to move the data from one MSSQL Server to another?

Description

This document will explain you on the steps involved in moving data from one MSSQL server to another. Database, could be moved from one drive to another or from one computer to another.

Note: If you wanted to move Desktop Central server from one location to another, refer to this.

Steps

There are three stages in moving the MSSQL database, they are:

1. Backup the existing Database
2. Create a New Database
3. Restore the data to the New Database

Note: Stop the DC Server and open command prompt as Administrator to perform the following steps.

Backup the existing Database

1. Take the backup of our product located at <Desktop Central Home>/bin directory, by using backuprestore.bat. You can take a backup of the existing database and store it, in your desired location.
   A backup file will be named using the buildnumber-date-time.zip format.
   For example, 90000-May-25-2014-13-26.zip where 90000 is the build number, May 25th 2014 is the date and 13:26 is the time.

Create a New Database

1. Change the new database server details located at <Desktop Central Home>/bin directory, by using the changedbserver.bat file.
   1. Select Database Type as SQL Server
   2. Specify the Host Name where SQL Server is installed
   3. From the list of available instances, select the instance on which you wish to run the database.
   4. You can choose to authenticate the database connection either using Windows Authentication or SQL Server Authentication. Choose the required authentication and provide the credentials.
   5. Click the checkbox to enable NTLM authentication.
   6. Click Save to save and complete the SQL Server Configuration. It may be noted that it will take a few minutes to configure the settings on the SQL Server.
2. Start the DC Server. Starting the server will create new tables and insert the default data in new DB Tables. (It is equivalent to a fresh setup.)

Restore the data to the New Database

1. Stop the DC Server, before the restoring the backup we need to perform the following:

   Go to the server installed location, and copy the database_params.conf file, this should be replaced in the archived backup. Follow the steps mentioned below:

   1. Go to "server-home/conf/database_params.conf" in the new setup, copy the "database_params.conf".
   2. Now, extract the archived backup zip, and navigate to the same location. For example : "Drive:\ManageEngine\DesktopCentral_Server\conf" and overwrite the file "database_params.conf"
   3. Select all the files under the unzipped folder, and Convert it to a zip file. Do not Zip the extracted folder directly, which will end up in creating an intermediate folder. Verify if the structure of the converted zip, is the same as :"ManageEngine\DesktopCentral_Server\conf".
2. Restore the data to the new MSSQL database located at <Desktop Central Home>/bin directory, by using backuprestore.bat. Locate the data that has been backed up, to restore it in the new MSSQL database.

Now start the Desktop Central server to see that the database has been moved to the desired location.

How To's