Moving the SEPM database to a new SQL server or instance using SQL backup/restore.

Article:TECH132761  |  Created: 2010-01-27  |  Updated: 2012-10-01  |  Article URL
Article Type
Technical Solution


You are planning on decommissioning your existing SQL server hardware and want to know how to move the SEP database to a new SQL server.


You can perform a SQL backup of sem5 and restore to a new SQL server or instance using the following procedure:

1. Proceed to backup the database by right clicking the sem5 database then choose Tasks, Back Up. Here you can choose to use the existing backup file, or for a smaller backup file, you can remove that path and designate a new path and filename.

2. Copy the backup file to the new SQL server and restore the database as sem5.

3. Once the database is restored to the new SQL server or instance, the sem5 database user must be deleted, and a new sem5 login must be created. This is because there is no sem5 login, and you cannot create a login for a database if that database user already exists.

Delete the sem5 database user:

1. Open the sem5 database and choose Security, Users
2. Locate and delete the sem5 user

Create a new login for sem5:

1. From the top of the hierarchy, open Security -> Logins
2. Right Click Logins and choose New Login
3. Login Name: sem5
4. Enter a password
5. Default database: sem5
6. On the left choose User Mapping and place a check next to sem5
7. In Database role membership for: sem5, place a check next to dbowner (public should already be checked).
8. Click OK

4. Connect the SEPM to the database:

At this stage you have 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.

5. Run the Management Server Configuration Wizard to point the SEPM to the new database.

6a. If SEPM is already installed: Choose the option to Reconfigure the Management server. When prompted, enter the credentials for the sem5 user created in the steps above.

6b. If SEPM is a new installation, the wizard will launch after the setup is complete. Choose the option to add an additional manager to an existing site. When prompted, enter server name(\instance name) of the new SQL server, and the credentials for the sem5 user created in the steps above.

NOTE: If you are installing a new SEPM, you must use the same version of SEPM as the original SEPM. If you install a newer version of SEPM, you will get an error that the schema is not compatible.

NOTE: This solution works for all versions of SEP and both SQL 2005 and SQL 2008.

Legacy ID


Article URL

Terms of use for this information are found in Legal Notices