Solved

How to delete all databases in SQLServer SSAS

Posted on 2015-01-14
5
109 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

15 Experts available now in Live!

Get 1:1 Help Now