Restore Databases in AS 2005 from CAB Files created in AS 2000

I have to restore some analysis services 2000 databases on AS 2005.

Is there some tool which can perform this?

Mathew,

To migrate AS2000 databases to AS2005, you can use the Migration Wizard (MigrationWizard.exe). For more info, please see: <http://msdn2.microsoft.com/en-us/library/ms143409.aspx>.

Hope this helps,

Artur

|||

I have CAB files being created by another team. Previously i used to restore them on my SQL 2000 Analysis Services using msmdarch.

Now i am planning to upgrade to SQL server 2005. But the team which creates the CAB files will continue to be on SQL 2000 Analysis Services for at least 6 months.

Therefore migrationwizard will not be helpful in my case. (I don't have access to their SQL Server or Analysis Server databases)

|||

If your machine already has AS2000 installed, you could install AS2005 on the same machine as a named instance and then restore the CAB files onto your local AS2000 server and use the migration wizard to migrate these databases from your AS2000 instance to the AS2005 instance on the same machine.

--Artur

|||

Hello,

I understand that the solution of Artur will work, if you have access to the relational database from which the cube is calculated because when you finish migrate the cube it has to be recalculated. I have the same problem (a few cubes that I′d like to try in 2005, but I don′t posess the access to the relational database). Is there any way of migrating the cab without the access to the relational db?

Regards

Restore Analysis Backup to new DAtabase Name

I have backed up an AS database. Now I want to restore it to a new name so I have two copies to play with. How do I do this?

Thanks,

Chris

Hi,

I'm not sure how to copy a SSAS database by backup/restore. However what about

- copy the VS project
- deploy the project with a different name

I guess that might be even better than just a copy of the database...|||Change the file name of the database backup then restore it. That's all|||

That won't work as we are looking at a way to automate deployment of around 2300 databases. The technique we use with SQL is to have a template database that is created as part of our build process and then we restore this with a new name for each unique instance.

This does not appear to work for Analysis Services. We even looked at restoring using the template name and then renaming the database. However while that changes the name it appears it does not change the ID preventing you from doing another restore.

|||That doesn't work. The database name is not based on the backup file name. It is encoded in the backup.|||Just an idea, I didn't have any time to try it, yet... But there is a way (as far as I remember) to script out a complete database... Save the script any modify it (i.e. the name of the database)... Then send the modified script to the server.. That might work...|||That is what I am doing but it is very different than our current practice for the relational stores so I was hoping someone else had hit this.

With this technique I have to get the operations guys to edit the XML which they have a high likelihood of hosing up...

Thanks,

Chris|||Chris,

Took me a while to work this out, but I think this should do.

Within BIDS, if you use the "Build" command instead of the "Deploy" command, you'll end up with a set of files in a \bin directory under your project's path. This directory contains a set of files that can be used to deploy the project manually using the deployment wizard, located by default at the following path:

c:\program files\microsoft sql server\90\tools\binn\vsshell\common7\ide\microsoft.analysisservices.deployment.exe

The files created are as follows:

<databasename>.asdatabase
<databasename>.configsettings
<databasename>.deploymentoptions
<databasename>.deploymenttargets

You can review each of these, but basically the first contains metadata to create the database (almost the equivalent of using the "Create Database As" scripting option in SSMS) and the other three contain settings that the deployment wizard will use to deploy the database. The last one is the key for you -- it contains the name of the server and the name of the deployed database, which can be different from the name given to the database in BIDS.

To work with these files, you have two options:

The first would be to have your operations folks modify the .deploymenttargets file manually, modifying the server and database names as needed. This file is MUCH simpler than a complete DDL script, so modifying it manually might be an option for you. The operations folks would them simply have to use the deployment wizard to deploy the database. In this case, the wizard would need two parameters --the name of the .asdatabase file plus a "/s" option, which tells the wizard to read the other files as input and do a deployment.

The second option would be to have the operations folks use the deployment wizard to update the files and then use it again to do a deployment. If you start the wizard with the name of the .asdatabase file plus a "/a" option, the wizard will ask a bunch of questions related to doing a deployment. The answers to the questions will be used to rewrite the other three files listed above. The wizard could then be run again with the .asdatabase name plus a "/s" option to do the deployment. This method would keep you from having to rely on someone correctly modifying the .deploymenttargets file manually.

You can find information about the deployment wizard in BOL...

Hope this helps.

Dave Fackler

PS. I did a test of this by using the "Build" option within BIDS on a copy of the Adventure Works DW solution. I then updated the .deploymenttargets file, updating the name of the database from "Adventure Works DW" to "My New Database". I then ran the deployment wizard as follows:

microsoft.analysisservices.deployment.exe "Adventure Works DW.asdatabase" /s

The wizard deployed the database to my server, where it showed up with the name "My New Database". I then checked via a "Create Database As" script option on the new database and both the name and the ID of the database reflected my new name.
|||Post CTP 15 builds will have a fix allowing you to restore database with different name. This could be done by running Restore command and specifying alternative Name.

--
This posting is provided "AS IS" with no warranties, and confers no rights|||

How do you get around the problem of credentials of multiple domains. Dev machine on one domain, server on anther (via VPN at client).

I know I could problably copy all the files to the server and change the configs.

Anybody researched this?

Thanks

Restore Analysis Backup to new DAtabase Name

I have backed up an AS database. Now I want to restore it to a new name so I have two copies to play with. How do I do this?

Thanks,

Chris

Hi,

I'm not sure how to copy a SSAS database by backup/restore. However what about

- copy the VS project
- deploy the project with a different name

I guess that might be even better than just a copy of the database...|||Change the file name of the database backup then restore it. That's all|||

That won't work as we are looking at a way to automate deployment of around 2300 databases. The technique we use with SQL is to have a template database that is created as part of our build process and then we restore this with a new name for each unique instance.

This does not appear to work for Analysis Services. We even looked at restoring using the template name and then renaming the database. However while that changes the name it appears it does not change the ID preventing you from doing another restore.

|||That doesn't work. The database name is not based on the backup file name. It is encoded in the backup.|||Just an idea, I didn't have any time to try it, yet... But there is a way (as far as I remember) to script out a complete database... Save the script any modify it (i.e. the name of the database)... Then send the modified script to the server.. That might work...|||That is what I am doing but it is very different than our current practice for the relational stores so I was hoping someone else had hit this.

With this technique I have to get the operations guys to edit the XML which they have a high likelihood of hosing up...

Thanks,

Chris|||Chris,

Took me a while to work this out, but I think this should do.

Within BIDS, if you use the "Build" command instead of the "Deploy" command, you'll end up with a set of files in a \bin directory under your project's path. This directory contains a set of files that can be used to deploy the project manually using the deployment wizard, located by default at the following path:

c:\program files\microsoft sql server\90\tools\binn\vsshell\common7\ide\microsoft.analysisservices.deployment.exe

The files created are as follows:

<databasename>.asdatabase
<databasename>.configsettings
<databasename>.deploymentoptions
<databasename>.deploymenttargets

You can review each of these, but basically the first contains metadata to create the database (almost the equivalent of using the "Create Database As" scripting option in SSMS) and the other three contain settings that the deployment wizard will use to deploy the database. The last one is the key for you -- it contains the name of the server and the name of the deployed database, which can be different from the name given to the database in BIDS.

To work with these files, you have two options:

The first would be to have your operations folks modify the .deploymenttargets file manually, modifying the server and database names as needed. This file is MUCH simpler than a complete DDL script, so modifying it manually might be an option for you. The operations folks would them simply have to use the deployment wizard to deploy the database. In this case, the wizard would need two parameters --the name of the .asdatabase file plus a "/s" option, which tells the wizard to read the other files as input and do a deployment.

The second option would be to have the operations folks use the deployment wizard to update the files and then use it again to do a deployment. If you start the wizard with the name of the .asdatabase file plus a "/a" option, the wizard will ask a bunch of questions related to doing a deployment. The answers to the questions will be used to rewrite the other three files listed above. The wizard could then be run again with the .asdatabase name plus a "/s" option to do the deployment. This method would keep you from having to rely on someone correctly modifying the .deploymenttargets file manually.

You can find information about the deployment wizard in BOL...

Hope this helps.

Dave Fackler

PS. I did a test of this by using the "Build" option within BIDS on a copy of the Adventure Works DW solution. I then updated the .deploymenttargets file, updating the name of the database from "Adventure Works DW" to "My New Database". I then ran the deployment wizard as follows:

microsoft.analysisservices.deployment.exe "Adventure Works DW.asdatabase" /s

The wizard deployed the database to my server, where it showed up with the name "My New Database". I then checked via a "Create Database As" script option on the new database and both the name and the ID of the database reflected my new name.|||Post CTP 15 builds will have a fix allowing you to restore database with different name. This could be done by running Restore command and specifying alternative Name.

--
This posting is provided "AS IS" with no warranties, and confers no rights|||

How do you get around the problem of credentials of multiple domains. Dev machine on one domain, server on anther (via VPN at client).

I know I could problably copy all the files to the server and change the configs.

Anybody researched this?

Thanks