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