[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

How to delete all databases in SQLServer SSAS

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
Lenny Gray
Asked:
Lenny Gray
  • 3
  • 2
1 Solution
 
Rainer JeschorCommented:
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
 
Lenny GrayAuthor Commented:
I am using SqlServer2008.

How do the SQL DBAs delete all databases?

Thanks for the help!

Lenny
0
 
Rainer JeschorCommented:
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
 
Lenny GrayAuthor Commented:
Thanks!

I'll await your testing, Rainer.

Lenny
0
 
Rainer JeschorCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now