Playing around with System.Data.SQLite...so what?

Being the geek that I am whenever I see a file type with an extension like .db, that just says "open me!' So with a quick use of the handy dandy file bottle opener Notepad.exe I discovered that it is a SQLite Format 3 file.

Why is this important? It's not really, its just more interesting than anything. I've been working on a few different projects related to SnapManager 8.0 for SharePoint and was writing a document on the media service and how it creates indexes. The focus on my investigation was around how SMSP 8 backs up the SharePoint Web-Front End servers and Application Servers via a stream-based copy from the servers to the media service LUN. During this process we backup the SharePoint 14 (2010) or 15 (2013) hive along with any customizations, the Global Assembly Cache (GAC), Web parts and also the SMSP installation directory (Manager and/or Agent).

 

Before you begin you will need to have an SMSP backup of a WFE or Application Server from the Media Service LUN. This is typically on your Application Server as many people create a shared model running SharePoint services and the SMSP Media Service on that server. Access your LUN (via drive letter or mount point) and look for a similar directory structure and contents as shown in the screenshot below. The file that you want is the .db file, in my case it is named appmed1WFE_index.db; this was a backup of my Application Server. Notice that this file is 130MB so be sure if you make a copy for local use to play around with that you have enough space.

 

In order to review the contents of the .db file it's pretty simple, follow these steps:

  1. Download the SQLite .NET Assemblies from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki. I used the 64-bit Windows (.NET Framework 4.5) setup that has the design-time components for Visual Studio 2012.
  2. Install the SQLite .NET Assemblies
  3. Open up Visual Studio 2012 and start a new Windows Forms project.
  4. In Solution Explorer add a Reference to the System.Data.SQLite.dll assembly.
  5. So what you should have so far looks something what is in the screenshot, in my case I named my form Simple SMSP Viewer for WFE/APPSERVER Backups
  6. Now let's wire up the form to the .db file to a DataGridView. From the Toolbox open up the Data tools section and double-click the DataGridView to add the control to the Windows Forms. This will also automatically open up the DataGridView Tasks to create the connection to the .db file. You should see something very similar to the below screenshot. To make sure that the DataGridView scales properly be sure to click Dock in Part Container.
  7. Time to Choose Data Source. Dropdown the list and click Add Project Data Source...
  8. This will start the Data Source Configuration Wizard to create the connection. Choose Database > Next
  9. Choose Dataset > Next
  10. Click New Connection...
  11. Click Change... to select the Data Source, in this case System.Data.SQLite Database File. You can see that I have my .db file stored in C:\temp.

  12. You can click Test Connection if you want to check everything is accessible, but if not click OK.
  13. Now the connection is wired up so we need to finish setting up the DataGridView, click Next.
  14. Choose which objects you want to view in the DataGridView. Select Tables by marking the checkbox next to Tables and click Finish.
  15. Once you click Finish the dialog will disappear and you will be taken back to the Windows Form with the DataGridView and no data source chosen. Dropdown the list and drill into the Other Data Sources until you find your newly created dataset. You should see something similar to the below screenshot.
  16. Now the DataGridView is wired up to the appmed1WFE_indexDataSet. You should have the indexDataSet and the DataSetBindingSource visible now (see screenshot).
  17. Click on the appmed1WFEIndexDataSetBindingSource (or whatever name you are using) to expose the Properties of the component. There is a DataMember property that needs to be set to view the contents of the .db file. Dropdown the list and choose either File or Folder to wire up the data member to the DataGridView.
  18. I chose File and automatically the DataGridView will refresh with all of the columns in the .db file. Hit F5 and voila! You are now looking at what was backed up as part of a WFE backup from SMSP without any coding.

 

Why would you ever want to do this? Curiosity, because you can, it's neat-o, but probably more likely a case of just knowing what's been backed up vs trusting it to the SMSP gods :-)

 

Cheers,

Barkz