How to archive and replace the Mailscape SQL Server database

Purpose

This article covers archiving the Mailscape SQL Server database and replacing it with a new, empty one — keeping a backup of the old database in case historical data is needed later.

These procedures apply to the SQL Server Mailscape database only. They do not work for the built-in Mailscape database on the EMS web server.

Before you start

Step 4 of the first procedure deletes the production Mailscape database. The backup taken in step 3 is the only copy of your monitoring and reporting history at that point. Do not begin unless you have confirmed the backup completed and you can see the .bak file on disk.

After the replacement, reporting and trend data starts from empty. Historical reports will have no data to draw on until new data accumulates.

The paths below assume default locations for SQL Server 2017 (MSSQL14). Other versions use different paths — check yours before running anything that names a path.

Procedure 1 — Archive and replace the existing database

Step 1: Disable SQL Integration (ENow web server)

1. In the ENow Admin Console, go to Reporting.
2. Click SQL Integration...
3. Click Disable SQL Integration.

Step 2: Close ENow processes (web server)

1. Close the ENow Admin Console.
2. In IIS Manager, stop MailscapeAppPool.
3. In services.msc, stop the ENow Web Service.
4. In services.msc, stop all other ENow services.

Step 3: Back up the Mailscape database to disk (SQL Server)

1. In SQL Server Management Studio, expand Databases. Do not expand the database itself in the tree view.
2. Right-click the Mailscape database and select Tasks > Back Up...
3. On the General page, set:
   • Database: Mailscape
   • Backup type: Full
   • Backup component: Database
   • Back up to: Disk
4. Note the location of the backup file. The default for SQL Server 2017 is:

   C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Mailscape.bak

5. Click OK and wait for the backup to complete.
6. Click OK to acknowledge completion, then close the Backup window.
7. Confirm the .bak file exists on disk at the path above before continuing.

Step 4: Delete the Mailscape database (SQL Server)

This is the destructive step. Do not proceed unless step 3 completed and you have verified the backup file.

1. In SQL Server Management Studio, expand Databases. Do not expand the database itself in the tree view.
2. Right-click the Mailscape database and select Delete.
3. On the General page, verify that Mailscape is the only object listed.
4. Select Close existing connections.
5. Click OK.

Step 5: Create a new Mailscape database (ENow web server)

1. In services.msc, start all ENow services.
2. In IIS Manager, start MailscapeAppPool.
3. In the ENow Admin Console, go to Reporting.
4. Click SQL Integration...
5. Complete the ENow SQL Integration Wizard to create the new database.

The Mailscape database is now archived and replaced.

Procedure 2 — Restore the archived database under a new name

Use this only if you need to view the old data. It restores the archive as MailscapeArchive, so it will not conflict with the live Mailscape database on the same instance.

Confirm the logical file names first

The restore below moves logical files named Mailscape and Mailscape_Log. Confirm those are the names in your backup, otherwise the restore fails:

RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Mailscape.bak';

Use the LogicalName values returned in the MOVE clauses below.

Run the restore

1. On the Mailscape SQL Server, open SQL Server Management Studio.
2. Click New Query.
3. Run the following, adjusting paths for your SQL Server version:

   USE [master]
   GO
   
   RESTORE DATABASE [MailscapeArchive]
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Mailscape.bak'
   WITH
   MOVE 'Mailscape' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MailscapeArchive_Data.mdf',
   MOVE 'Mailscape_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MailscapeArchive_Log.ldf',
   RECOVERY, STATS = 10;

4. Click Execute and wait for the restore to complete.
5. In Object Explorer, right-click Databases and select Refresh.
6. Confirm both Mailscape and MailscapeArchive are present.

Applies to

Tested with EMS 7.13 (7.13.0.169). The procedure should apply to most versions, but confirm the SQL Server paths for your own installation.