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: https://stackoverflow.com/questions/1064557/creating-a-file-name-as-a-timestamp-in-a-batch-job

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

CREATE:
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

DEPLOY:
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:

:: THIS CODE WILL DISPLAY A 2-DIGIT TIMESTAMP FOR USE IN APPENDING FILENAMES

:: CREATE VARIABLE %TIMESTAMP%

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

 


 


 

Advertisements