Backup all SSAS databases using TSQL
-
Comments posted to this topic are about the item Backup all SSAS databases using TSQL
-
Hello Theo - can I use your script at work? hopefully you dont mind. I need to backup some SSAS databases automatically 🙂
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
-
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.
-
If you need something with more options, like compress, upload to the cloud (amazon s3, glacier, dropbox, azure). You can try http://www.sqlserverbooster.com and it's free
-
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 🙂
-
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.
-
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
-
Interesting idea....
I'll look into that too 🙂
-
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:
Date4/16/2015 8:54:02 AM
LogJob History (Maint_SSAS Backup All SSAS Databases)
Step ID2
ServerDOM-SQL-PROD
Job NameMaint_SSAS Backup All SSAS Databases
Step NameDynamically Build XMLA commands Per Database
Duration00:04:16
Sql Severity17
Sql Message ID7412
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
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! -
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
-
The only way I've been able to retrieve SSAS metadata to the extent that I can get separate ID and Name properties for SSAS databases is to use the AMO. You can access AMO via vbscript and the like, and also Powershell.
-
Theo Ekelmans (4/20/2015)
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
Thanks for checking in Theo;
I'm more of a SQL guy than Analysis Services, but circumstances beyond my control are making me expand my boundaries quickly.
i found it looks like it's a lot easier just to rebuild the darn database by scripting it out, and then processing it again so it populates with data.
at the script point, if it didn't do it automatically, i can modify they xlma script and then run it so the name and Id are identical.
that seems to be the direction i'll end up going, so my version of your great script would not need to be modified.
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! -
The easiest solution would be to add DatabaseId to the DBSCHEMA_CATALOGS rowset.
I put in a Connect item for it a while ago but it doesn't seem to be a prevalent need. It just seems to me like DatabaseId is a very basic and essential attribute, why leave it out of cube metadata rowsets?
https://connect.microsoft.com/SQLServer/Feedback/Details/789357
-
Thanks for the script.
-
Hi,
First of all thanks for the script.
Should I skip the link server execution if my AS and engine is on the same instance? THanks
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply