Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backup all SSAS databases using TSQL Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 8:07 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 29, 2015 12:08 PM
Points: 53, Visits: 606
Comments posted to this topic are about the item Backup all SSAS databases using TSQL
Post #1436242
Posted Thursday, May 16, 2013 11:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 3:04 PM
Points: 10, Visits: 26
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
Post #1453692
Posted Friday, May 31, 2013 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 13, 2015 7:38 AM
Points: 3, Visits: 252
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.
Post #1458743
Posted Friday, September 12, 2014 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 10, 2015 2:29 PM
Points: 4, 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
Post #1613039
Posted Friday, September 12, 2014 8:36 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 29, 2015 12:08 PM
Points: 53, Visits: 606
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 :)
Post #1613059
Posted Friday, September 19, 2014 7:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 10, 2015 2:29 PM
Points: 4, 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.
Post #1615481
Posted Thursday, November 13, 2014 7:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 17, 2015 5:30 AM
Points: 16, Visits: 345
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
Post #1634149
Posted Friday, November 14, 2014 10:18 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 29, 2015 12:08 PM
Points: 53, Visits: 606
Interesting idea....

I'll look into that too :)
Post #1634693
Posted Thursday, April 16, 2015 7:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 13,712, Visits: 35,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1677591
Posted Monday, April 20, 2015 9:29 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 29, 2015 12:08 PM
Points: 53, Visits: 606
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
Post #1678534
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse