Remove old modelstore from an upgraded AX2012 R2 database

One of the benefits of AX2012 R2 compared to AX2012 RTM is the fact that they decided to split the database in two parts, one part for the business data and one part for the application data. This makes it a lot easier to copy business data from production to test without overwriting the application data in test. Sure, there were ways around this in RTM, but you can't deny it is a lot easier to do this now. Another benefit is that you no longer have the application (modelstore) residing in the business database. This means you don't need to have a full backup with point-in-time recovery for the production application. The application in RTM would be aroud 2.5 GB exported, but inside the database you would have huge indexes and also the compiled assemblies stored. All of these data are now stored in the application database in R2. I would argue that there is no need to have the recovery mode set to "Full" on these databases containing the application. I mean, each time you import a modelstore, have a look at the database log. It will grow huge.

Inspired by a response from Kevin Kidder at the Community Site, I decided to try make a Stored Procedure that removes the modelstore elements inside the database. Getting rid of that old modelstore from RTM that now sits in the business database after successfully upgrading to R2 will reduce the size of the database with a couple of GB, so why not.

I found this nice script to remove all schema elements, and used is as a base for my script. I also initialized a new modelstore for the purpose of testing my script. Actually, the SQL scripts being used for creating a modelstore is available as resources in the AxUtilLib assembly. You can study them by using any tool that can revese engineer a .Net dll.

This procedure might not be flawless, so use it with care. Obviosuly; take a backup and test. By default the procedure will print out the SQL statements you need to run in order to remove all the modelstore elements in the database. You will have to explicitly pass "w" for it to execute the statements for you. You will have to decide if you want to evalute the output and run it manually, or let the procedure do it all.

Please report feedback and/or errors back to me. :-)

Here it is:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241

CREATE PROCEDURE [DBO].[CleanUpModelStore] (   @WORKTEST CHAR(1) = 't'  -- use 'w' to work and 't' to print ) AS /*-----------------------------------------------------------------------------------------      Author : Tommy Skaue   Date:    06.04.2013      Description: Drops all modelstore elements.   Usage and License: Free and at own risk   Parameter: t = test/print out              w = work and execute statements         Report any errors or feedback to tommy@skaue.com.       Inspired by the script made by Ranjith Kumar S   http://ranjithk.com/2010/01/31/script-to-drop-all-objects-of-a-schema/    -------------------------------------------------------------------------------------------*/ BEGIN      DECLARE @SQL VARCHAR(5000) DECLARE @MODELSTOREELEMENTQUERY VARCHAR(2500) DECLARE @MSG VARCHAR(500) DECLARE @SCHEMANAME VARCHAR (50) = 'DBO'    IF OBJECT_ID('TEMPDB..#DROPMODELSTORE') IS NOT NULL DROP TABLE #DROPMODELSTORE CREATE TABLE #DROPMODELSTORE (    ID INT IDENTITY(1,1)   ,SQLSTATEMENT VARCHAR(5000)  )    -- Common filter for ModelElements SELECT @ModelStoreElementQuery =  'AND (T.NAME LIKE ''Model%'' OR   T.NAME IN (    ''axIdAllocsTable'',    ''AxIdAsk'',    ''existingPaths'',    ''Sources'',    ''newPaths'',    ''SourceMerge'',    ''SYSXPPASSEMBLY'',    ''ElementTypes'',    ''SchemaVersion'',    ''Layer'',    ''LayerVersioning'',    ''GlobalFieldIdPool''))'    -- removes all the foreign keys that reference a PK in the target schema  SELECT @SQL =   'SELECT        ''ALTER TABLE ''+SCHEMA_NAME(FK.SCHEMA_ID)+''.''+OBJECT_NAME(FK.PARENT_OBJECT_ID)+'' DROP CONSTRAINT ''+ FK.NAME   FROM SYS.FOREIGN_KEYS FK   JOIN SYS.TABLES T ON T.OBJECT_ID = FK.REFERENCED_OBJECT_ID   WHERE T.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMANAME + ''')     AND FK.SCHEMA_ID <> T.SCHEMA_ID     ' + @MODELSTOREELEMENTQUERY + '   ORDER BY FK.NAME DESC'     --IF @WORKTEST = 't' PRINT (@SQL )  INSERT INTO #DROPMODELSTORE  EXEC (@SQL)      -- drop all default constraints, check constraints and Foreign Keys  SELECT @SQL =  'SELECT        ''ALTER TABLE ''+SCHEMA_NAME(T.SCHEMA_ID)+''.''+OBJECT_NAME(FK.PARENT_OBJECT_ID)+'' DROP CONSTRAINT ''+ FK.[NAME]   FROM SYS.OBJECTS FK   JOIN SYS.TABLES T ON T.OBJECT_ID = FK.PARENT_OBJECT_ID   WHERE T.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMANAME + ''')    AND FK.TYPE IN (''D'', ''C'', ''F'')' + @MODELSTOREELEMENTQUERY      --IF @WORKTEST = 't' PRINT (@SQL )  INSERT INTO #DROPMODELSTORE  EXEC (@SQL)     -- Common filter for ModelElements SELECT @MODELSTOREELEMENTQUERY =  'SELECT OBJECT_ID FROM SYS.OBJECTS SO2 WHERE   SO2.PARENT_OBJECT_ID IN   (    SELECT SO3.OBJECT_ID FROM SYS.OBJECTS SO3 WHERE SO3.TYPE IN (''U'') AND    (     SO3.NAME LIKE ''Model%'' OR     SO3.NAME IN (      ''axIdAllocsTable'',      ''AxIdAsk'',      ''existingPaths'',      ''Sources'',      ''newPaths'',      ''SourceMerge'',      ''SYSXPPASSEMBLY'',      ''ElementTypes'',      ''SchemaVersion'',      ''Layer'',      ''LayerVersioning'',      ''GlobalFieldIdPool'')    )   )   OR   (    SO2.TYPE IN (''U'') AND    (     SO2.NAME LIKE ''Model%'' OR     SO2.NAME IN (      ''axIdAllocsTable'',      ''AxIdAsk'',      ''existingPaths'',      ''Sources'',      ''newPaths'',      ''SourceMerge'',      ''SYSXPPASSEMBLY'',      ''ElementTypes'',      ''SchemaVersion'',      ''Layer'',      ''LayerVersioning'',      ''GlobalFieldIdPool'')    )   )   OR   (    SO2.TYPE IN (''V'') AND    (    SO2.NAME LIKE ''SysModel%'' OR    SO2.NAME LIKE ''Util%''     OR    SO2.NAME LIKE ''Model%''    OR    SO2.NAME IN (''ConfigurationKeys'', ''LicenseCodes'', ''Origins'',''SECURABLEOBJECT'') OR    SO2.NAME IN (     ''SECURITYENTRYPOINTLINK'',     ''SECURITYPERMISSION'',     ''SECURITYROLE'',     ''SECURITYROLEEXPLODEDGRAPH'',     ''SECURITYROLEPERMISSIONOVERRIDE'',     ''SECURITYROLETASKGRANT'',     ''SECURITYSUBROLE'',     ''SECURITYSUBTASK'',     ''SECURITYTASK'',     ''SECURITYTASKENTRYPOINT'',     ''SECURITYTASKEXPLODEDGRAPH'',     ''SECURITYTASKPERMISSIONOVERRIDE''     )    )   )   OR   (    SO2.TYPE IN (''TR'') AND    SO2.NAME IN (     ''ModelSecurityPermission_CreateSecurable'',     ''UpdateChangedBy'',     ''SetInstalledAndChangedBy'',     ''UpdateModelFromManifestChangedBy'',     ''IO_Trig_INS_ModelElement'',     ''IO_Trig_INS_ModelElements'',     ''IO_Trig_Del_ModelElements'',     ''ModelSecurityCommon_Insert''     )   )   OR   (    SO2.TYPE IN (''P'') AND    (    SO2.NAME LIKE ''XI_%'' OR    SO2.NAME LIKE ''XU_%''    )    )   OR   (    SO2.TYPE IN (''FN'', ''TF'',''IF'',''FS'',''FT'') AND    SO2.NAME IN (     ''CreateTemplateName'',     ''GetLayerMask'',     ''GetNextAvailableFieldOrIndexAxId'',     ''GetNextAvailableAxId'',     ''GetAxIdHole'',     ''IsAxIdExcluded'',     ''SECURITYROLE_FUNC'',     ''SECURITYTASK_FUNC''    )   )'  -- drop all other objects in order   SELECT @SQL =   'SELECT  CASE   WHEN SO.TYPE=''PK'' THEN ''ALTER TABLE     ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+OBJECT_NAME(SO.PARENT_OBJECT_ID)+'' DROP CONSTRAINT ''+ SO.NAME   WHEN SO.TYPE=''U''  THEN ''DROP TABLE      ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]   WHEN SO.TYPE=''V''  THEN ''DROP VIEW       ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]   WHEN SO.TYPE=''P''  THEN ''DROP PROCEDURE  ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]           WHEN SO.TYPE=''TR'' THEN ''DROP TRIGGER    ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]   WHEN SO.TYPE IN (''FN'', ''TF'',''IF'',''FS'',''FT'')                          THEN ''DROP FUNCTION   ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]  END FROM SYS.OBJECTS SO  WHERE SO.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMANAME + ''')   AND SO.TYPE IN (''PK'', ''FN'', ''TF'', ''TR'', ''V'', ''U'', ''P'')   AND SO.OBJECT_ID IN (' + @MODELSTOREELEMENTQUERY + ') ORDER BY  CASE   WHEN TYPE = ''PK'' THEN 1         WHEN TYPE IN (''FN'', ''TF'', ''P'',''IF'',''FS'',''FT'')                      THEN 2         WHEN TYPE = ''TR'' THEN 3         WHEN TYPE = ''V''  THEN 4         WHEN TYPE = ''U''  THEN 5         ELSE 6     END'    --IF @WORKTEST = 't' PRINT (@SQL ) INSERT INTO #DROPMODELSTORE EXEC (@SQL)    DECLARE @ID INT, @STATEMENT VARCHAR(2000) DECLARE STATEMENT_CURSOR CURSOR FOR SELECT SQLSTATEMENT     FROM #DROPMODELSTORE     ORDER BY ID ASC        OPEN STATEMENT_CURSOR  FETCH STATEMENT_CURSOR INTO @STATEMENT     WHILE (@@FETCH_STATUS = 0) BEGIN   IF @WorkTest = 't'    PRINT (@statement)   ELSE    BEGIN     PRINT (@statement)     EXEC(@statement)    END       FETCH STATEMENT_CURSOR INTO @STATEMENT     END     CLOSE STATEMENT_CURSOR  DEALLOCATE STATEMENT_CURSOR     PRINT '------- ALL - DONE -------'   END GO

Hope this helps someone.