Solved

How to delete all databases in SQLServer SSAS

Posted on 2015-01-14
5
94 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:ZogFromNewJersey
  • 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:ZogFromNewJersey
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:ZogFromNewJersey
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now