How to move the data from one MSSQL Server to another?
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 : This document is applicable only for customers using Desktop Central build 92076 or older versions. If you are using later versions, you will have to follow these steps.
There are three stages in moving the MSSQL database, they are:
- Backup the existing Database
- Create a New Database
- 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
- 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
- Perform the below steps in the computer where the new MS SQL Server is installed,
- Choose Start --> Run and type services.msc.
- Ensure that "SQL Browser" service has been started. If not, start the service.
- Open the SQL Server Configuration Manager from the Start menu
- Expand SQL Server Configuration Manager (Local) and navigate to SQL Server Network Configuration.
- Choose the Instance on which you wish to run the database and check if TCP/IP ports are enabled. If not, right-click on TCP/IP and enable it.
- You should restart the SQL Service after you enable TCP/IP. Select SQL Server Services from the left pane, right-click the instance and choose Restart.
- Change the new database server details located at <Desktop Central Home>/bin directory, by using the changedbserver.batfile.
- Select Database Type as SQL Server
- Specify the Host Name where SQL Server is installed
- From the list of available instances, select the instance on which you wish to run the database.
- You can choose to authenticate the database connection either using Windows Authentication or SQL Server Authentication. Choose the required authentication and provide the credentials.
- Click the checkbox to enable NTLM authentication.
- 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.
- 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
Note : The following steps needs to be performed before initiating the migration process
- 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.conffile, this should be replaced in the archived backup. Follow the steps mentioned below:
- Go to "server-home/conf/database_params.conf" in the new setup, copy the "database_params.conf".
- 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"
- 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".
- 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.