Backup and Restore Analysis Services Database to another Server

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mhakyil 39502 Mr or Mrs. 500 Points: 542 More actions · Answer 1

i use kind of solution as below. they are scheduled in sql server agent, running every night once.

you should select "SQL Server Analysis Services Command" in the Job Properties - Step - Type

for backup:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>OLAP</DatabaseID>

</Object>

<File>\\10.10.10.10\e\Live\DWH\OLAP_backup.abf</File>

<AllowOverwrite>true</AllowOverwrite>

</Backup>

for restore:

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<File>\\10.10.10.10\e\Live\DWH\OLAP_backup.abf</File>

<DatabaseName>OLAP</DatabaseName>

<AllowOverwrite>true</AllowOverwrite>

<DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">F:\OLAP\</DbStorageLocation>

</Restore>

Robert Klimes SSCoach Points: 17865 More actions · Answer 2

you could also use powershell: Backup-ASDatabase and Restore-ASDatabase

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

george sibbald SSC Guru Points: 104210 More actions · Answer 4

IIRC if you perform the restore via the GUI you can also script it out.

---------------------------------------------------------------------