Restore-ASDatabase cmdlet

Restores a multidimensional or tabular database from an Analysis Services backup (.abf) file.

Syntax

Restore-ASDatabase [-RestoreFile] <string> [-Name] <string> [-AllowOverwrite <SwitchParameter>] Locations <Microsoft.AnalysisServices.RestoreLocation[]>] [-Security <Microsoft.AnalysisServices.RestoreSecurity>] [-Password <System.SecureString>] [-StorageLocation <System.string>] [-Server <string>] [-Credential <PSCredential>] [<CommonParameters>]

Description

Enables an Analysis Services system administrator to restore a multidimensional or tabular database from a backup (.abf) file. If the file you are restoring was encrypted, use –FilePassword to provide the password that is used to decrypt the file.

This cmdlet supports the –Credential parameter, which can be used if you configured the Analysis Services instance for HTTP access. The –Credential parameter takes a PSCredential object that provides a Windows user identity. IIS will then impersonate this user when connecting to Analysis Services. The identity must have system administrator permissions on the Analysis Services instance to restore the file.

Parameters

-RestoreFile <string>

Specifies the path and name of the file to restore. If you specify just the file name, without a path, the default backup location is assumed.

-Name <string>

Specifies the Analysis Services database to be restored.

-AllowOverwrite <SwitchParameter>

Overwrites a database that uses the same name and location.

-Locations <Microsoft.AnalysisServices.RestoreLocation[]>

Specifies the remote location of the partitions to be restored.

-Security <Microsoft.AnalysisServices.RestoreSecurity>

Represents the security settings used for the restore operation. Valid values are CopyAll, SkipMembership, IgnoreSecurity. CopyAll restores roles and membership. SkipMembership recreates just the role. IgnoreSecurity restores the database, without roles.

-Password <SecureString>

Specifies a password to be used to restore an encrypted backup file. You must specify the password that was originally used to encrypt the file.

-StorageLocation <string>

Specifies the database storage location. This is the location of the database files on the file system. Set this parameter if you are not using the default location, which is the backup folder of the target instance.

-Server <string>

Specifies the Analysis Services instance to which the cmdlet will connect and execute. If no server name is provided, a connection will be made to localhost. For default instances, specify just the server name. For named instances, use the format servername\instancename. For HTTP connections, use the format http[s]://server[:port]/virtualdirectory/msmdpump.dll.

-Credential <PSCredential>

Specifies a PSCredential object that provides a Windows user name and password. Specify this parameter only if the Analysis Services instance is configured for HTTP access, using Basic authentication. For native connections that use integrated security, this parameter is ignored.

If this parameter is present, the credentials that it provides are appended to the connection string. IIS will impersonate this user identity when connecting to Analysis Services. If no credentials are specified, the default windows account of the user who is running the tool will be used.

To use this parameter, first create a PSCredential object using Get-Credential to specify the username and password (for example, $Cred=Get-Credential “adventure-works\admin”. You can then pipe this object to the –Credential parameter (-Credential:$Cred).

For more information about authentication and credential usage, see Analysis Services PowerShell. For more information about HTTP access, see Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 7.0.

<CommonParameters>

This cmdlet supports the common parameters: -Verbose, -Debug, -ErrorAction, -ErrorVariable, -OutBuffer, and -OutVariable. For more information, see About_CommonParameters.

Inputs and Outputs

The input type is the type of the objects that you can pipe to the cmdlet. The return type is the type of the objects that the cmdlet returns.

Example 1

PS SQLSERVER:\SQLAS\localhost\default> restore-asdatabase awtest.abf testawrestoredb –security:CopyAll

This command restores an Analysis Services backup file (awtest.abf) in the local backup folder to the local Analysis Services default instance. The database name does not have to exist; in this case, the database name is specified as part of the restore operation. Adding –Security:CopyAll populates roles and role membership from the backup database to the new restored database.

Example 2

PS SQLSERVER:\SQLAS\Localhost\default > $pwd = read-host –AsSecureString –Prompt “Password” 
PS SQLSERVER:\SQLAS\Localhost\default > $pwd -is [System.IDisposable] 
PS SQLSERVER:\SQLAS\localhost\default> restore-asdatabase –restorefile testdb.abf –name AWTEST2 –password:$pwd
PS SQLSERVER:\SQLAS\Localhost\default >$pwd.Dispose()
PS SQLSERVER:\SQLAS\Localhost\default >Remove-Variable –Name pwd

Lines 1 and 2 are used to prompt for the password that was used to encrypt the file.

Line 3 restores an encrypted Analysis Services backup file (testdb.abf) from a local backup folder of an Analysis Services default instance.

Lines 4 and 5 remove the password.

Example 3

PS SQLSERVER:\SQLAS\localhost\default> restore-asdatabase –restorefile “\\myremoteserver\backups\testdb.abf” –name Contoso_Retail –server myremoteserver –storagelocation “\\myremoteserver\restoreDBFiles”

This command restores an encrypted Analysis Services backup file (testdb.abf) in a remote backup folder on a remote Analysis Services default instance. The –StorageLocation parameter is used to place the database files in a non-default location, in this case a file shared named restoreDBfiles.

See Also

Concepts

Analysis Services PowerShell

Other Resources

Manage Tabular Models Using PowerShell