Solved

How to delete all databases in SQLServer SSAS

Posted on 2015-01-14
5
182 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

734 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