SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup all SSAS databases using TSQL


Backup all SSAS databases using TSQL

Author
Message
Theo Ekelmans
Theo Ekelmans
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 802
Comments posted to this topic are about the item Backup all SSAS databases using TSQL
Oswaldo Morales
Oswaldo Morales
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 26
Hello Theo - can I use your script at work? hopefully you dont mind. I need to backup some SSAS databases automatically Smile

Also what does this do? http://schemas.microsoft.com/analysisservices/2003/engine"

I tested in my local computer with no access to the internet and the scrip still worked fine.


Thanks' -
Oswaldo
kvetter
kvetter
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 257
This passes the DatabaseName where the DatabaseId should go. This can cause problems for databases that do not have the same value for DatabaseName and DatabaseId.
ErikV
ErikV
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 27
If you need something with more options, like compress, upload to the cloud (amazon s3, glacier, dropbox, azure). You can try www.sqlserverbooster.com and it's free
Theo Ekelmans
Theo Ekelmans
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 802
Hi Erik,

I knew of this tool, but this one requires running a seprate service on the SQL server, which is fine if you are allowed do that.

If not... you could use my script Smile
ErikV
ErikV
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 27
It's true. But remember that you can disable the service and just run the GUI that also makes the backups. And also exists another options. You can install it in a different machine and makes remotely the backups. All this effort worth it if you want to upload to the cloud, move it through the network, encrypt or something else more complex.

You script it's clean and elegant.
marcelo miorelli
marcelo miorelli
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 407
Hi Theo,
I am successfully backing up SSAS databases following you idea.
It was definitely not straight forward, I had a few issues
as you can see here
and also here

But in my case:
1) I don't have sql server installed on the same machine that I have ssas
2) I don't have ssas installed on the same machine that I have sql server

Now the new challenges are:
1) how do you do a RESTORE VERIFYONLY in SSAS
2) how do you do a DBCC CHECKDB in SSAS


Another thing that I needed done is to delete old SSAS backup files.
this you can see on this link, I have done it using PowerShell, from a remote computer.
It deletes files older than 2 days.
All the scripts are there.


regards
Marcelo
Theo Ekelmans
Theo Ekelmans
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 802
Interesting idea....

I'll look into that too Smile
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70940 Visits: 40924
Theo thanks for the script!
i got it to work on a regular basis on most of my servers that have SSAS, but I've got an exception on one SSAS database: the database was renamed at one point, so the ID is different from the name; the linked server can give us the name of the Database, which we assume is the same as the ID that the xmla script requires.

i end up getting this error:

Date 4/16/2015 8:54:02 AM
Log Job History (Maint_SSAS Backup All SSAS Databases)

Step ID 2
Server DOM-SQL-PROD
Job Name Maint_SSAS Backup All SSAS Databases
Step Name Dynamically Build XMLA commands Per Database
Duration 00:04:16
Sql Severity 17
Sql Message ID 7412
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user:MyDomain\dwhc. Could not execute statement on remote server 'SSASLocal'. [SQLSTATE 42000] (Error 7215) OLE DB provider "MSOLAP" for linked server "SSASLocal" returned message "Errors in the metadata manager. Either the database with the ID of 'ClaimTracking' does not exist in the server with the ID of 'DOM-SQL-PROD', or the user does not have permissions to access the object.". [SQLSTATE 01000] (Error 7412). The step failed.


so the script gets created for ClaimTracking in my case, but the ID the XMLA script should have is technically a different value than the name.



have you encountered this since you wrote your article? how did you tackle it?

the data from $SYSTEM.DBSCHEMA_CATALOGS doesn't have the id, would you know if a different system catalog has the id tied to the name, so the script could be modified?

Edit: i did my due diligence, and ran exec sp_tables_ex [SSASLocal] on the linked server, and then queries every $SYSTEM table, and visually poked around for the ID, but i had no luck:


SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM TABLE_SCHEM.TABLE_NAME') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_TABLES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_COMMAND_OBJECTS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_COMMANDS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_CSDL_METADATA') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_DB_CONNECTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_DIMENSION_STAT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_ENUMERATORS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_INSTANCES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_JOBS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_KEYWORDS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_LITERALS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_LOCKS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_MASTER_KEY') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_MEMORYGRANT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_MEMORYUSAGE') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PARTITION_STAT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PROPERTIES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_RESOURCE_POOLS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_SCHEMA_ROWSETS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_SESSIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACE_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACE_DEFINITION_PROVIDERINFO') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACE_EVENT_CATEGORIES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRANSACTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_XEVENT_TRACE_DEFINITION') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_FUNCTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT_PMML') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODEL_XML') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODELS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_SERVICE_PARAMETERS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_SERVICES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_STRUCTURE_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_STRUCTURES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_CUBES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_FUNCTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_HIERARCHIES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_INPUT_DATASOURCES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_KPIS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEMBERS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_PROPERTIES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_SETS') AS a



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Theo Ekelmans
Theo Ekelmans
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 802
Hi Lowell,

I've never renamed a SSAS DB, so... no i have not had the misfortune to run into this error.

I would like to promise here that i will look into this issue on short notice, but alas at the moment we run desperately short of staff, and having to train "the new guys" does'nt help either.

But if i do run into this problem or if i get some time to tweak my scripts i *will* look into this, because i was lucky not to have ran into it.

grtz,

Theo
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search