How to automatically export/backup the MDS model

I noticed that I was making backups of the MDS model manually, so it’s time to automate that in a basic but effective way 🙂
When using CMD, it’s always tricky to get the correct timestamp (which I’ll use in the output filename). If found a nice script on:

Normally when you would like to import or export/backup a MDS model, you’ll use these scripts:

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration
MDSModelDeploy createpackage -service MDS1 -model "Biml meta-data" -version "VERSION_1" -package "E:\MDS\BimlMetaData_20170804.pkg" -includedata

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration
MDSModelDeploy deployclone -package "E:\MDS\BimlMetaData_20170804.pkg" -service MDS1


To automate this, create a BATCH-file (*.bat) in your local ‘MDS backup directory’ (in my case ‘E:\MDS\’) and copy/paste the following script into it:



for /f "tokens=1-8 delims=.:/-, " %%i in ('echo exit^|cmd /q /k"prompt $D $T"') do (
for /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^|date') do (
set dow=%%i
set mm=%%j
set dd=%%k
set yy=%%l
set hh=%%m
set min=%%n
set sec=%%o
set hsec=%%p

:: ensure that hour is always 2 digits

if %hh%==0 set hh=00
if %hh%==1 set hh=01
if %hh%==2 set hh=02
if %hh%==3 set hh=03
if %hh%==4 set hh=04
if %hh%==5 set hh=05
if %hh%==6 set hh=06
if %hh%==7 set hh=07
if %hh%==8 set hh=08
if %hh%==9 set hh=09

:: assign timeStamp:
:: Add the date and time parameters as necessary - " yy-mm-dd-dow-min-sec-hsec "

set timeStamp=%yy%%mm%%dd%_%hh%%min%%sec%

:: --------- TIME STAMP DIAGNOSTICS -------------------------

:: Un-comment these lines to test output

:: echo dayOfWeek = %dow%
:: echo year = %yy%
:: echo month = %mm%
:: echo day = %dd%
:: echo hour = %hh%
:: echo minute = %min%
:: echo second = %sec%
:: echo hundredthsSecond = %hsec%
:: echo.
:: echo Hello!
:: echo Today is %dow%, %mm%/%dd%.
:: echo.
:: echo Your timestamp will look like this: %timeStamp%
:: echo.
:: echo.
:: echo.
:: pause

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

MDSModelDeploy createpackage -service MDS1 -model "Biml meta-data" -version "VERSION_1" -package "E:\MDS\BimlMetaData_%timeStamp%.pkg" -includedata


IMPORTANT: Make sure that you change the ‘MDS backup directory’ in the code above.

Now create a scheduled task (standard Windows functionality) that executes the BATCH-file and runs either on a specific time and/or system startup:

















Other handy scripts:
PowerShell: Delete old files based on a given retention period (days) #maintenance

Use PowerShell to script all database objects





MDS: How to create or deploy a package from a model

A few lines I need to remember for MDS 🙂


CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

MDSModelDeploy createpackage -service MDS1 -model “Biml meta-data” -version “VERSION_1” -package “C:\MDS\BimlMetaData_20170614.pkg” -includedata


CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

MDSModelDeploy deployclone -package “C:\MDS\BimlMetaData_20170614.pkg” -service MDS1

MDS Create package from model

How to: Delete records from your MDS Entity table

Here’s an example script which you could use to delete records from your MDS Entity/table:

Delete records from your MDS entity table


Here’s the code:

--Start clean:
TRUNCATE TABLE stg.[Actuals_Leaf_Leaf]

--Retrieve CodeID's:
INSERT INTO stg.[Actuals_Leaf_Leaf] (ImportType,ImportStatus_ID,BatchTag,Code)
4 AS ImportType
,0 AS ImportStatus_ID
,'Delete-20160310' AS BatchTag
FROM mdm.Actuals_Leaf a
WHERE a.Year = 2016
AND a.Month = 2

SELECT * FROM stg.[Actuals_Leaf_Leaf]

EXEC stg.[udp_Actuals_Leaf_Leaf] @VersionName = 'VERSION_1', @LogFlag = 0, @BatchTag = 'Delete-20160310'

--Verify results:
FROM mdm.Actuals_Leaf a
WHERE a.Year = 2016
AND a.Month = 2

--End clean:
TRUNCATE TABLE stg.[Actuals_Leaf_Leaf]


More info on:




MDS: How to truncate an Entity-table?

Here’s how you can quickly (with using the GUI or Excel Add-in) truncate an Entity-table in MDS by using the accompanied Staging-table (stg_*):


MDS needs the Code-ids together with the ImportType = 4 to delete records via staging.
Basically the first step is to populate the staging table by using the MDS view (which contains the current set of records and thus the Code-ids), after that you’ll need to execute the accompanied MDS stored procedure to process these changes.
Important: make sure that both BatchTag’s are identical!

Here’s the T-SQL code:

TRUNCATE TABLE stg.[TableName_Leaf]

INSERT INTO stg.[TableName_Leaf] (ImportType,ImportStatus_ID,BatchTag,Code)
4 AS ImportType
,0 AS ImportStatus_ID
,'Delete-20151102' AS BatchTag
FROM mdm.JOBS_TableName

SELECT * FROM stg.[TableName_Leaf]

EXEC stg.[udp_TableName_Leaf] @VersionName = 'VERSION_1', @LogFlag = 0, @BatchTag = 'Delete-20151102'

More info about working with MDS staging tables (and ETL):

MDS database upgrade failed, ‘user does not have access to the application’ (db version

Yesterday a colleague upgraded our reporting server (SQL Server 2012 SP1 CU6) to CU7 (build: 11.0.5623), which should be stable since it is out for like a month now.
(More info aboud builds? Check out:

As always MDS requires a database upgrade (because we’ve upgraded the ‘client‘ to a new version, as in ‘the SQL Server instance‘):





Sure, let’s do that.

So I RPD-ed into our reporting server. Right-click on the ‘Master Data Services Configuration Manager‘-icon in the start-menu and clicked ‘Run as Administator‘ (important one!).
And indeed, it does require an upgrade 🙂







Next step is to select the correct database and hit the ‘Upgrade‘-button….but…it threw me an exception:



Hit ‘OK‘ and ‘Cancel‘, notice that the database version jumped to ‘‘?:





What I found was that whatever you try next, the wizard keeps throwing the same exception over and over again when you restore the MDS database and try again.

The thing is that, after a while I just ignored the exception and went to the MDS interface with IE and somehow things are working properly.


So, when you’re upgrading the MDS database from ‘‘ to ‘‘ ignore the following exception from the wizard:
‘…The user does not have access to the application…’

Because the user does have access and the script did run correctly 🙂



The use and misuse of Master Data Services (MDS) in SQL Server

After watching the SQL Hangout (Hangout #27 Master Data Management?), something told me to also shed light on other ways to use and misuse MDS.

Let start with the definition of MDS (by Microsoft):

Master Data Services (MDS) is the SQL Server solution for master data management. Master data management (MDM) describes the efforts made by an organization to discover and define non-transactional lists of data, with the goal of compiling maintainable master lists…The result of a successful MDM solution is reliable, centralized data that can be analyzed, resulting in better business decisions.

So…master data….check! You may immediatly think about a single version of the truth regarding customers, products, etc.
But in practice I found out that thanks to its brilliant (spoiler: a bit slow in 2012, but should be fixed in 2016) Excel add-in, business users can easily upload and maintain fairly static datasets. The web interface is horrible, you really need to get used to the way it’s been arranged. I still have figured out how the versioning-system works btw, but just create a subscription view and then use it as a source for your DWH. Beats building SSIS packages for each and every Excel-sheet the business user wants to ‘upload’.

Business users of my current client are use MDS for:
Master Data (list of branches, clusters, geo-locations, etc.)
Hierarchies (GL account, e.g. Profit & Loss or Balance sheet)
Targets for SSRS reporting (e.g. min/max target values per branch for a certain KPI)
Figures which are uploaded by the business, like:
* Actuals (e.g. illness rates, number of FTE’s, etc.)
* Budgets (e.g. P&L, etc.)
* Forecasts (e.g. P&L, etc.)

MDS wasn’t build for this and it isn’t fast when you want to publish upload or retrieve 295.356 records (via the MDS Excel Add-In).

MDS - Many many records
(Yep I’m Dutch)

But whatever…if it works….it works 🙂

An example of our list of views (MDS subscription views):

Use and misuse of Master Data Services 2012 - MDS

Want to learn more about MDS? Follow Kenneth Nielsen‘s blog:

MDS: How to change the data type in an existing Entity (filled table)

Want to change the data type of an existing Entity’s attribute without reloading of re-creating that entity?

This is just a little trick you should remember, you’re unable to change the data type via the MDS web interface (GUI):

Unable to change the data type in MDS

However, this is possible in the MDS Excel Add-in! 🙂

MDS change attribute data type in excel unable via webinterface gui

Here’s how you could you could do it:

1) Open Excel, connect to MDS and retrieve the complete set of data from MDS for that specific Entity

2) Select a random cel in the column of the attribute of which you would like to change the attibute’s data type.

3) In the MDS-tab in Excel, click on ‘Attribute Properties‘ and simply change the data type into the type you want.
After that it will publish the update and give some kind of message that you need to update your subscription. Sorry my message was in Dutch so…but simply said you need to refresh/rec-create the View was made under ‘Integration Management‘ in MDS.

4) Go to ‘Integration Management‘ in MDS (web interface or GUI), click on ‘Create Views‘ and Edit that specific view. When in Edit-mode simply clicking on the Save-button will do the trick.

5) Verify in SSMS that that view now contains the changed data type (in the ‘MDS’ database on the SQL Server instance).

That’s it!
Now don’t forget that when you’re using SSIS to ETL the data…it requires a meta-data update 😉