How to move the Symantec Endpoint Protection Manager SQL database to a new SQL Server with backup and restore
Last Updated April 12, 2019
How to move a Symantec Endpoint Protection Manager (SEPM) SQL database to a new SQL server by backing-up and restoring the database.
Perform a SQL backup of the database and restore it to a new SQL server or instance using the following procedure:
Back up and restore the database:
Open the SQL Server Management Studio for the current server
Right-click the sem5 database and choose Tasks > Back Up Note: Choose to use the existing backup file or for a smaller backup file remove that path and designate a new path and file name.
Copy the backup file to the new SQL server
Open the SQL Server Management Studio for the new server
Right-click Databases in the server tree and click Restore Database
Using the Device option, locate and restore the .bak file that was created in step 2
Once the database is restored to the new SQL server or instance, the sem5 and reporter database users must be deleted and a new sem5 login must be created. This process is because there is no sem5 login and a login for a database can't be created if that database user already exists.
Delete the sem5 and reporting users (SQL Authentication method only):
Note: These instructions assume that the default naming conventions are in use. If the previous instance was not named sem5 the database user names are different. The reporting user appears similar to 'REPORTER_databasename'.
Open the database
Click Security > Users
Locate and delete the sem5 user
Locate and delete the REPORTER_sem5sem5 user, if it exists (SEP 14.x only).
Create a new login for sem5 (SQL Authentication method only):
From the top of the hierarchy, open Security > Logins
Right-click Logins and choose New Login
Use the login name: sem5
Choose 'SQL Server authentication'
Enter a password
Enforce password expiration
User must change password at next login
Select sem5 as the default database
On the left, click User Mapping, and then check sem5
In Database role membership for: sem5, check dbowner
On the right under default schema make sure that this matches the schema if changed from the default (dbo) Note: Verify the current schema in use by expanding the tables folder under the sem5 database object and looking at the table prefix example (dbo."TableName")
Click Search on the Securables page and add the option for "The server 'servername'" and click OK
Under "Explicit", find 'Alter any login' and check the box for 'Grant'
Connect the SEPM to the database
At this stage you either installed a new SEPM, or plan to use the same SEPM as before. In either case, the SEPM needs to be pointed to the new database location.
Run the Management Server Configuration Wizard to point the SEPM to the new database.
If SEPM is already installed:
Choose the option to Reconfigure the Management server.
When the credentials prompt appears, enter the credentials for the sem5 user that was created in the previous steps.
If SEPM is a new installation, the wizard launches after the setup is complete:
Choose the option to add an additional manager to an existing site.
When the prompt appears, enter server name(\instance name) of the new SQL server, and the credentials for the sem5 user that was created in the previous steps.
If installing a new SEPM use the same version of SEPM as the original SEPM. If installing a different version of SEPM, an error shows that the schema is not compatible.
For Windows Authentication method: If the account to use is a domain user account make sure that the Endpoint Protection Manager computer and the SQL server are in the same domain (or a trusted domain). Also make sure that the domain user account exists in its domain controller. If the account in use is a local user account, make sure that the Endpoint Protection Manager computer and the SQL server are in the same workgroup, that the same local user account exists on both computers (same name and password), and that the account has local Administrator privileges on both computers.
Imported Document ID: TECH132761
Subscribing will provide email updates when this Article is updated. Login is required to Subscribe