Day 26 of 31 Days of SQL Server Backup and Restore using PowerShell: Restoring SQL Server Analysis Services databases

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:

ParameterTypeDescription
BackupFileStringPath to backup file
DatabaseNameStringName of database to restore as (default is to restore with same name as backed up from
OverWriteBooleanWhether to overwrite an existing db, default is False
RestoreLocationAn array of RestoreLoctions for restoring remote dbs
RestoreSecurityRestoreSecurity EnumSpecifies what to do with security roles after the restore (Copy, Ignore, Skip)
PasswordStringPassword to restore a password protected backup
StorageLocationsStringSpecify 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.

Tagged ,

Leave a Reply

Your email address will not be published. Required fields are marked *