Manage Databases in Analysis Services

Overview

The Analysis Services engine stores data in databases. Managing these databases—creating, backing up, restoring, and deleting—is essential for maintaining a healthy BI environment.

Prerequisites

  • SQL Server Analysis Services (SSAS) instance installed and running.
  • Permissions: Process, Read, and Admin role membership on the target instance.
  • SQL Server Management Studio (SSMS) or Azure Data Studio with the Analysis Services extension.

Create a Database

Using SSMS

  1. Open SQL Server Management Studio and connect to the SSAS instance.
  2. Right‑click the Databases node > New Database….
  3. Enter a name, configure model storage mode, and set initial storage locations.
  4. Click OK to create the database.

Using TMSL (Tabular Model Scripting Language)

{
  "createOrReplace": {
    "database": {
      "name": "SalesModel",
      "id": "c5f2a3e2-7c15-4b4a-9bf2-1e5d5e3b9e12"
    }
  }
}

Execute the script via Invoke-ASCmd in PowerShell or the XMLA endpoint.

Backup a Database

SSMS Method

  1. Right‑click the database > Back Up….
  2. Specify a .abf file location.
  3. Optionally select Compress backup and Include partitions.
  4. Click OK.

PowerShell / XMLA

Invoke-ASCmd -Server "localhost" -Database "SalesModel" -Query "
BACKUP DATABASE [SalesModel] TO  
   URL = N'file:///C:/Backups/SalesModel.abf'  
   WITH COMPRESSION = TRUE;
"

Restore a Database

SSMS Method

  1. Right‑click the Databases node > Restore….
  2. Browse to the .abf file.
  3. Set the target database name and storage locations.
  4. Click OK to start the restore.

PowerShell / XMLA

Invoke-ASCmd -Server "localhost" -Query "
RESTORE DATABASE [SalesModel] FROM  
   URL = N'file:///C:/Backups/SalesModel.abf'  
   WITH REPLACE;
"

Delete a Database

SSMS Method

  1. Select the database, press Delete or right‑click > Delete.
  2. Confirm the deletion.

PowerShell / XMLA

Invoke-ASCmd -Server "localhost" -Query "DROP DATABASE [SalesModel]"

Best Practices

  • Schedule regular full backups and retain at least three generations.
  • Test restore procedures quarterly on a non‑production server.
  • Use descriptive database names and include version information when appropriate.
  • Document storage locations for backups and ensure they are on separate media.
  • Apply the ROW_LEVEL_SECURITY role before granting user access.