Solved

How to delete all databases in SQLServer SSAS

Posted on 2015-01-14
5
132 Views
Last Modified: 2016-02-18
This script deletes one database. But I have over 200 cubes that need deletion.

How do I modify this script to delete all databases?

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <DatabaseID>ALM_Prototype_MR3_Valuation_Yield_Curve_FX_Base</DatabaseID>
    </Object>
</Delete>

Thanks!
0
Comment
Question by:Lenny Gray
  • 3
  • 2
5 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 40550577
Hi,
sorry but dynamically / looping  is not possible in Xmla. You will have to create an object element for each database.
There are a couple of alternative ways:
you can use either SSIS, Powershell or .Net code to query SSAS server to return a list of all databases and then loop through this list and run delete statements.
Which SQL server version?
Which way would you prefer?
Thanks and HTH
Rainer
0
 

Author Comment

by:Lenny Gray
ID: 40550916
I am using SqlServer2008.

How do the SQL DBAs delete all databases?

Thanks for the help!

Lenny
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 40551114
Hi,
imho deleting all databases is not a daily job of a DBA, therefore I guess that this will be done through a manual created, verified script where someone added all existing databases.

If it needs to be done in a quick (and repeatable) way, I prefer to either code it into a tool or use Powershell with SQL Server Management objects, because there I can construct loops.

Or you simply open SSMS, connect to your SSAS Instance, click on the databases node in the object explorer and then use the window "Object Explorer Details" where you should get a list of all databases. Mark all and then right-click -> Delete.

I will try to create a sample script after work tonight.

HTH
Rainer
0
 

Author Comment

by:Lenny Gray
ID: 40553417
Thanks!

I'll await your testing, Rainer.

Lenny
0
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 500 total points
ID: 40560289
Hi,
sorry - busy project.

Here is the Powershell script - it is hard to test as I have currently no test machine available - but it should work:
# Load SSAS DLL
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
# Create SSAS Server Object
$serverAS = New-Object Microsoft.AnalysisServices.Server
# Connect to the SSAS Server instance (using either name\Instance or IP:Port)
$serverAS.connect("127.0.0.1:9314")
# Displays all database names (further properties like last processed etc. are also available)
$serverAS.databases | select Name

# The next view lines do the Drop
#foreach($db in $serverAS.databases) {
#  $db.Drop()
#}

Open in new window

HTH
Rainer
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question