i4connected Knowledgebase

Moving the SQL Database to a different SQL Server

Abstract

Learn what needs to be done to properly move the SQL Database to a different SQL Server.

This article aims to guide the users in moving the SQL Database to a different SQL Server. The described procedure implies backing up the source SQL Database, restoring the backed up database on the target SQL Server and updating all connection strings to point to the new location of the database.

Before describing the procedure of moving the SQL Database, it important to mention that the target SQL Server must have a version compatible with the database to be moved.

Backing up the SQL Database:

The steps below describe the backup procedure that must be executed on the source database in order to obtain the backup file that can be restored on the target SQL Server.

  1. Open the Microsoft SQL Server Management Studio and connect to the appropriate instance of the Database Engine.

  2. In Object Explorer, expand server tree then expand the Databases node.

  3. Right-click on the desired database and select Tasks > Back Up.

    Capture4195.jpg
  4. In the Back Up Database dialog, make sure that the Backup type is set to Full and set the Destination to the folder where you want the backup file to be deployed.

    Capture4199.jpg
  5. In the Options tab of the Back Up Database dialog, go to Overwrite media and select Overwrite all existing backup sets.

    Capture4200.jpg
  6. Click OK to start the backup procedure. The execution progress will be displayed in the bottom-left corner of the dialog.

    Capture4198.jpg
  7. A dialog will inform you when the process is complete. Press OK to close the Back Up Database dialog.

    The backup file (.BAK) will be available in the folder set in the Destination section of the Back Up Database dialog.

Restoring the SQL Database on the target SQL Server:

To restore the previously backed up database on the target SQL Server, the backup file (.BAK) must be copied locally. The following steps will describe the restore procedure.

  1. On the machine running the target SQL Server, copy the SQL database backup file (.bak) to Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup (where MSSQLSERVER - marked with italic fonts - is the SQL instance name).

  2. Open the SQL Server Management Studio and connect to the Database Engine server.

  3. Right-click on the Databases to open the contextual menu. Select Restore Database....

    Capture973.jpg
  4. In the Restore Database dialog, select Device and browse for the backup (.bak) file.

    Capture974.jpg
  5. In the Select backup devices dialog, make sure that the Backup media type is set to File and click Add to browse for the backup file.

    Capture975.jpg
  6. The Locate Backup File dialog will point you to the exact location of the backup file (copied earlier to the backup folder of the SQL Server). Select the backup file and press OK. Confirm the Select backup devices dialog also. If the dialog doesn't point directly to the backup file location, use the left-side tree structure to locate the backup file on your hard drive.

    Capture976.jpg
  7. Back in the Restore Database window, another option is needed if the backup to be restored will overwrite an existing database:

    Tip

    Select the Options page from the left-side menu and enable the Overwrite the existing database (WITH REPLACE) option.

    Capture977.jpg
  8. Click OK to start the restore process. A dialog will confirm the success of the restore operation.

Updating the Connection Strings:

After the database has been restored, the connections strings for Analysis Services, Portal, Server and Reporting Server must be updated to point to the new location of the database.

Any other exiting connections to the database must be updated!

Updating the Analysis Services connection string
  1. On the machine running the SQL Server with Analysis Services, open the Microsoft SQL Server Management Studio. Connect to the appropriate Analysis Services instance.

  2. Go to the Analysis Services database > Data Sources and double-click the appropriate data source to open the Data Source Properties dialog.

    Capture983.jpg
  3. In the Data Source Properties, Select the Connection String and press the browse (...) button to open the Connection Manager.

    Capture4201.jpg
  4. In the Connection Manager window, insert the IP (or name) of the machine running the target SQL Server where the database has been restored. To connect to a named instance on the target SQL Server, add the instance name after a backslash character. For example: TARGET-SQLSERVER-PC\pEMS.

    Best practice: use SQL Server Authentication in order to avoid security issues. This is not required but recommended.

    Capture4202.jpg
  5. At the Connect to a database section, choose Select or enter a database name and insert the name of the database restored on the target SQL Server.

    Capture4204.jpg
  6. Click on Test Connection to check if the new settings are working. Click OK to confirm the connection.

Update the Portal connection string
  1. Go to the i4connected installation location (by default C:\Program Files\WEBfactory\Data Warehouse\[CUSTOMER_NAME]\[SW_NAME]).

  2. Open the Portal folder and edit the web.config configuration file using any preferred text editor.

  3. Locate the <connectionStrings> node and the connection string named DataWarehouseSQLConnectionString inside it.

    <add name="DataWarehouseSQLConnectionString" connectionString="Data Source=.\WEBFACTORY2010DW;Initial Catalog=WEBfactory.DWH.Data.Database;Persist Security Info=True;User ID=sa;" providerName="System.Data.SqlClient" />
  4. Edit the connectionString's Data Source attribute to point to the new SQL Server running the restored database and the appropriate database instance name.

    <add name="DataWarehouseSQLConnectionString" connectionString="Data Source=TARGET-SQLSERVER-PC\pEMS;Initial Catalog=WEBfactory.DWH.Data.Database;Persist Security Info=True;User ID=sa;" providerName="System.Data.SqlClient" />
  5. Edit the connectionString's Initial Catalog attribute to point to the correct database.

    <add name="DataWarehouseSQLConnectionString" connectionString="Data Source=TARGET-SQLSERVER-PC\pEMS;Initial Catalog=WEBfactory.DWH.Data.Database.Example;Persist Security Info=True;User ID=sa;" providerName="System.Data.SqlClient" />
  6. Save and close the file.

  7. Repeat the steps 3, 4, 5 and 6 for both the WEBfactory DWH Server.exe.config configuration file from the Server folder and WEBfactory.DWH.Reporting.Server.exe.config configuration file from the Reporting folder.

    If other clients connect to the database, all their connection strings must be updated!