Yesterday we looked at backing up SQL Server Analysis Services databases, and today we’ll look at restoring them.

Just as with backups, prior to SQL Server 2012 SQLPS restores had to be done a little differently. Unlike Backup which was a method of a Database object, Restore is a method of the Server class. This makes sense, as if you’re restoring a database it won’t necessarily exist for you to create an object from. In it’s simplest form you just need to provide the location of the backup file:

Import-Module SQLPS -DisableNameChecking

$SSASInstance = "SSASServer1\Instance1"

$BackupFile = "c:\backups\db1.abf"

$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.connect($SSASSInstance)

$SSASServer.Restore($BackupFile)

However the Restore method can take more parameters to allow for more flexibility, in much the same way as we saw with the Backup method yesterday. For Restore these are:

Parameter Type Description
BackupFile String Path to backup file
DatabaseName String Name of database to restore as (default is to restore with same name as backed up from
OverWrite Boolean Whether to overwrite an existing db, default is False
RestoreLocation An array of RestoreLoctions for restoring remote dbs
RestoreSecurity RestoreSecurity Enum Specifies what to do with security roles after the restore (Copy, Ignore, Skip)
Password String Password to restore a password protected backup
StorageLocations String Specify a different storage location for the restored db

These can also be specified by creating a RestoreInfo object and passing that to the Restore method. Just as with the Backup method yesterday to supply a value you must provide the preceeding values as well, even if it’s just empty strings or NULL.

With the release of the SQL Server 2012 PowerShell provide MS also release a Restore-ASDatabae cmdlet. This can make the restores much simple to automate:

Import-Module SQLPS -DisableNameChecking

$SSASInstance = "SSASServer1\Instance1"
$BackupFiles = Get-ChildItem "c:\SSASbackups\"

foreach ($file in $BackupFiles){
    Restore-ASDatabase -Sever $SSASInstance -RestoreFile $file.fullname -AllowOverwrite
}

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.