How to Move MachPanel MS SQL Database To Another New MS SQL Server

Summary

This article tells you how to move "MachPanel_DB" MachPanel MS SQL Database to another new MS SQL server.

Applies To

MachPanel all versions

Solution

Note:  You can only move databases between same version database servers or from a lower version to higher version. For example, if existing database is running under SQL Server 2005, you must can restore the backup to SQL Server 2005 or SQL Server 2008, but you cannot restore backup from SQL Server 2005 to SQL Server 2000.

The process to move MachPanel database to a different server is as under:

Step 1: Take Backup (Existing MS SQL Server where MachPanel Database Exists)

  1. Click Start >> Programs >> Microsoft SQL Server 2008 >> SQL Server Management Studio Express.
  2. MS SQL Management studio window will open up asking for Login and Password, login as Windows Authenticated User or SA.
  3. After login navigate to: Computer name (SQLServer) >> Databases >> MachPanel_Db.
  4. Right click on MachPanel_Db and click Back Up under Tasks and take full backup of your database on disk, for example on C:\.

Step 2: Create Database on new MS SQL server

  1. Click Start >> Programs >> Microsoft SQL Server 2017 >> SQL Server Management Studio Express.
  2. MS SQL Management studio window will open up asking for Login and Password, login as Windows Authenticated User or SA.
  3. Create new Database Login named MachPanel_Db by clicking on New Login… under Computer name (SQLServer) >> Security >> Logins. This user should not be a SysAdmin and should only be an Owner for the MachPanel_Db database.

i. Select SQL Server Authentication and set password.
ii. Note down this password for use when configuring system database in upcoming Step 5.
iii. Navigate to: Computer name (SQLServer) >> Databases.
iv. Create a new database by Right Clicking on Databases and selecting New Database…
v. Select the new user created above in 3, MachPanel_Db as Owner of new database. Name the Database MachPanel_Db.
​​

Step 3: Restore Database on new MS SQL server
  1. Move to 4 if you are already inside SQL Server Management Studio Express.
  2. Click Start >> Programs >> Microsoft SQL Server 2017 >> SQL Server Management Studio Express.
  3. MS SQL Management studio window will open up asking for Login and Password, login as Windows Authenticated User or SA.
  4. Right click Databases and click on Restore Database.
  5. Select the Backup file, that was created in Step 1.

 Step 4: Fix Database User.

  1. Run following SP in MS SQL query analyzer for MachPanel_Db.

EXEC sp_change_users_login 'Auto_Fix', 'MachPanel_Db'

Syntax for this SP is: EXEC sp_change_users_login 'Auto_Fix', 'user'

Step 5: Configure MachPanel System Database using Configuration Studio.

  1. Navigate to Configure >> System Database.
  2. Enter details of new database server and click Update Settings.

Important Note:  Once all above steps are completed, your control panel is not connected using the NEW SQL Server. So, ensure to set up regular backups for database on this new server and also better to shutdown old server after making sure everything is working to avoid any confusions
Posted 15 Years Ago, Updated 5 Years Ago
https://kb.machsol.com/Knowledgebase/50138/How-to-Move-MachPanel-MS-SQL-Database-To-Another-New-MS-SQL-Server