I think I have officially become a bi-weekly blog.  My current schedule isn’t going to be able to support a weekly blog right now.  The Restore-AXDatabase custom PowerShell function will restore a SQL Server database from a backup file.  This is used in my custom build process to reset the build environment back to a “vanilla” instance.

A development build environment needs to be reset to an instance that is missing all of your build code before starting the new build.  It is possible to delete the build models and recompile/resync everything to get back to “vanilla” but it is significantly faster to make a database backup of the AX database and model database without your code present and then restore these.  It was taking me 5-20 minutes to delete models and 90-150 minutes to recompile and resync everything.  It generally takes me 2-5 minutes to restore the 2 databases.

The Restore-AXDatabase function takes between 2-6 parameters:

• ServerInstance (Server and SQL instance where the DB is located.  Defaults to the local default instance.)
• AXDBName (Name of the database to be restored.)
• BackupFilePath (The backup file to be restored.)
• AdditionalSQLRestore (This defaults to the values that I use for SQL 2012.  I have added it as a parameter so it could be changed if necessary.)
• TimeOut (The value defaults to 10 minutes but can be overridden if desired)
• SMTPServer (SMTP server to use to send the email)
• MailMsg (Net.Mail.MailMessage object used to send the email)
• VariablePath (Path to a file used to default the parameters/variables)

This function can be found in Codeplex.  The steps of this function are:

• Load the variables if a VariablePath parameter is used
• Set up the SQL command
• Restore the DB using Invoke-SqlCmd
• Send the success/fail message

There is some setup that needs to be done to use this function.  The first thing necessary it to load the PowerShell module that contains it.  I wrestled with adding this to the function itself but decided against it.  The reasoning is that if you are using an earlier version of SQL Server than 2012, you need to load the SQL PowerShell Snap-ins vs loading the module.

I have added 3 lines to my profile to accomplish this.  The first and third line are added to make up for a side effect of loading the SQLPS module, it changes your location in PowerShell.  The Push-Location cmdlet pops your location onto a stack and the Pop-Location cmdlet retrieves it.  This allows you to load the SQLPS module without changing your location.

The other step that you will need to do is to get your build environment cleaned up and create backups of the AX database and the model database.  You are going to want to have only the models that aren’t part of your build installed in AX.  In my case, this would be my SYS, SYP, ISV & ISP layers.  I only have a single custom model in my VAR layer that I build.  I compile the AOT, IL and sync the database to make sure everything is ready.  I then take a backup of both my AX database and my model database.  Now I can restore both databases and use my Clean-AXArtifacts custom Powershell function to clean up all of the compiled IL and when I start my AOS back up, I have a working “vanilla” instance of AX for my build.