Things I had to do to test dbatools, Part 2

My last entry chronicled some of the work I did to get a 'development environment' up and going for the dbatools project. Here are the rest of the details, including the issue with the "BatchParser" DLL error.

What I did to get the dbatools Pester tests going on my VM:

• I checked out a copy of the appveyor project that sqlcollaborative has. This seems to be a requirement for testing with the 'official' dbatools tests, even if you aren't going with a full-blown appveyor workflow. If you want to write your own Pester tests, you can do anything you want to.
• I created a custom version of configure.ps1 and put it in c:\temp. This informs the Pester tests about my specific environment.
• I set my SQL Server 2008 R2 and SQL Server 2016 test instances to allow SQL Logins. I do not allow SQL logins on my instances if I can avoid it, but it makes sense to test dbatools features that support SQL logins.

Those changes let me run all of the tests without any critical "Pester is broken" problems. Unfortunately, there were still over 200 failures. All of them, or at least the vast majority of them, seem to be issues with my environment. Can't blame dbatools or Pester for that.

Since my immediate interest is in Restore-DbaDatabase, I needed to get that Pester test working before I started changing things. So, I switched focus to just getting that one test working perfectly. Here are the two important things I did to get Restore-DbaDatabase.Tests.ps1 working:

• I changed permissions on certain file system folders so that my "normal" login can see files in those folders. Prior to my doing this, the Restore-DbaDatabase test would fail. This is a development machine and this should be harmless (until I move to another machine and forget that I have to do this.) Those folders should be pretty familiar to any DBA. They were
• C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA
• C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Log
• Here's the show-stopper: the test for "page restore" gave me an error:

WARNING: [Invoke-DbaAdvancedRestore][11:52:21] Failed to restore db PageRestore, stopping | Exception calling "ExecuteNonQuery" with "1" argument(s): "Could not load file or assembly 'Microsoft.SqlServer.BatchParser,Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified." 

This lead to the failure of the "page restore" test.

This error took me a while to figure out. The file does exist on my system, it is part of the dbatools distribution. The file also in my GAC, but it's a much older version. 

After a couple of hours of stepping through code, googling, swearing, wondering why some of my breakpoints weren't being hit, fiddling with Add-Type, fiddling with LoadWithPartialName() and wondering why the DLL was unloadable, I found a guy with the same problem. He had a fix, which is to install the Visual C++ Redistributable Packages for VS 2013. 

I installed the 32 bit and 64 bit versions of the redistributable package, just to be safe. One reboot later, and everything seems hunky-dory now. I can run Restore-DbaDatabase.Tests.ps1 without any problems.

So: thanks, guy! 

I presume that most people are working with Windows 10 or maybe they've got full-blown visual Studio installs on their machines and already have those VC++ packages on their systems, making me an oddball. I'm documenting this, here, in case some other oddball has the same issue.