SQL BAK Explorer 2.0 released

We just released SQL BAK Explorer 2.0 today, and the significant addition is that of a Query Window.

As some of you may know, SQL BAK Explorer stores backup details of the files it reads in a SQL Server Compact database.  There are 5 tables used to store the details i.e.

  • backupset
  • backupmediaset
  • backupmediafamily
  • backupfile
  • backupfilegroup

The structure of these tables are similar to the same tables found in the msdb database, so if you’ve ever queried for backup details in msdb, you’ll feel right at home here.

So in the Query Window, you simply enter a query to retrieve the backup details you want e.g.

If you want to return multiple result sets, end each query with a semi-colon, and start each query on a new line e.g.

The SQL syntax for SQL Server Compact is fairly similar to the SQL Server syntax, so if you are already familiar with SQL Server query syntax, writing for SQL Server Compact should be easy.

SQL BAK Explorer 1.0 released

We just released SQL BAK Explorer, which is a useful tool to work with SQL Server backup files.  Basically, it can read the backup details off the backup files without requiring SQL Server.  The details it can provide are almost similar to what you can retrieve using the RESTORE HEADERONLY and RESTORE FILELISTONLY commands.  It supports backup files created using SQL Server 2005 up to SQL Server 2017.  However, it doesn’t support encrypted backup files.

Beyond just listing out the backup details, you can also view the details of multiple backup files simultaneously, in a summary window.  This makes it easy to compare backup set details.

Since we already have the backup details, SQL BAK Explorer can also generate the restore script for you.  The restore script for a full database backup will include the MOVETO options, so that you can easily move the database files around.  Hints are also provided on how much disk space is required.

Each time a backup file is read, SQL BAK Explorer stores the backup details in a local database.  The next time the same file is selected, the details are read from the database instead of re-reading the file again.  This allows the backup details to be displayed very fast.  In addition, SQL BAK Explorer can search this database for files making up a complete backup media set, when generating the restore script for a backup that has been split across multiple files.

 

If there is anything you would like to see added to SQL BAK Explorer, please do drop us a line at support@yohz.com.

Download the free 14-day trial now, or learn more about SQL BAK Explorer.

SQL Blob Viewer 4 – email notifications added

→ This article refers to SQL Blob Viewer, which has now been renamed to SQL Image Viewer.  The techniques described in this blog is still applicable, as the functionality of the product remains the same.  Only the name has changed.

SQL Blob Viewer 4 was released last month, with a bunch of usability improvements.

One of it is the ability to send email notifications for the export process.  With the Professional Edition, you can schedule export jobs to run using the Windows Task Scheduler.  Previously, the job would run, and a log generated of the export process.  If there were any errors raised, you would only know about it if you inspected the log.

With email notifications, you can now receive a copy of the log via email.  You can also choose to have emails to be sent only when errors are raised.  The email options are configured in the Export Wizard.

logs_emails_01

Before email notifications can be sent, you would need to set up your SMTP mail settings first.

email_settings_01

 

Linux and Wine compatibility

Here at Yohz Software, we receive the occasional email asking if our PostgreSQL-centric products run on Linux.  Being Windows applications, our products will run on Linux but requires Wine (the software, not the drink).  DB Doc has been able to run on Ubuntu since 2014, and we recently installed and ran PgComment and SQL Multi Select on Ubuntu 17.04 and Fedora 25.

SQL Multi Select on Fedora 25 and PlayOnLinux:

DB Doc on Ubuntu and Wine.

There are the occasional GUI quirks, which we try to fix as soon as possible, which is why you sometimes see different text and/or images if you run the same application on Windows.  If you discover any quirks/inconsistencies we have overlooked, please do drop us a line at support@yohz.com.

PostgreSQL statistics

PostgreSQL has a neat set of pg_stat views from which you can obtain statistics for tables, indexes, etc.  From DB Doc 4.2 and PgComment 1.2 onwards, you can now view the database, table, and index statistics in the DB Doc reports and in PgComment object inspector.

In DB Doc, database statistics are displayed on the database properties page

table statistics are displayed on each tables’ properties page

and index statistics (idx_scan, idx_tup_read, idx_tup_fetch) are displayed as part of the index definitions.

If you choose not to display the statistics, just deselect the Display database, table, and index statistics option.

 

PgComment, our free tool to easily add/modify comments to your PostgreSQL objects, displays the statistics details in the object inspector window.

– database statistics

 

– table statistics

 

– index statistics

SQL Multi Select on Ubuntu 17.04 and Wine 2.0.1

Well, SQL Multi Select mostly runs on Ubuntu 17.04 with Wine 2.0.1.  Instructions for installing Wine 2.0.1 on Ubuntu 17.04 can be found here.

What works:

  • running multiple statements in a script
  • using scripts located on Linux file system
  • displaying images in result set
  • using the command line interface to run projects
  • connecting to PostgreSQL databases via SSL connections
  • exporting results to text files and SQLite database

What doesn’t work:

  • clicking on the summary file to open it – you’ll need to manually locate and open the file
  • help file won’t open because it’s in a chm format – use the online help instead (http://www.yohz.com/help/dbdoc/index.html)

If you come across any other functionality that doesn’t work on Ubuntu, please do drop us a line at support@yohz.com.

Why use SQL Multi Select in addition to pgAdmin 4

Let me start by saying pgAdmin 4 is a great tool for PostgreSQL.  It makes a lot of the admin and monitoring work easier.  It’s also cross-platform, and it’s free.  On the other hand, SQL Multi Select is just a query tool, that runs only on Windows.  Well, it runs on Ubuntu using Wine, and on Fedora via PlayOnLinux.    The Basic Edition is free, and here are some reasons you may want to use SQL Multi Select as your PostgreSQL query tool instead of pgAdmin.  Note that pgAdmin 4 (1.4) was used for this comparison.

Multiple server targets

You can run queries on multiple PostgreSQL databases simultaneously with SQL Multi Select.  For example, say you want to run a script that returns every databases’ size.  Just set up a group containing the databases you want to run the query on:

Set up the scripts on run on each of those databases:

Run the script, and you’ll see the results for each of those databases, across all servers in the server group.

Multiple result sets

When you run the following in pgAdmin:

only the last result set is returned.

In SQL Multi Select, the same script returns multiple result sets.

To make things clearer, you can place comments before each individual script:

and SQL Multi Select will use those comments to identify the result sets instead of arbitrary numbers.

Image display

In bytea columns storing images, pgAdmin displays the hex values.

SQL Multi Select displays a thumbnail of the image (gif, jpeg, png, bmp, dicom), and some image properties.  Note that SQL Multi Select inspects the bytea values and identifies the image format automatically.  There is no need to provide another column that tells SQL Multi Select the image format.

 

Blob data identification

In bytea columns containing files, pgAdmin displays the hex values.

SQL Multi Select displays details of the data contained in those columns.  Note that SQL Multi Select inspects the bytea values and identifies the data type accordingly.

Result sets containing bytea columns return very slow

If your query returns bytea columns a few MBs in size, pgAdmin takes an extremely long time to return the result set when compared to SQL Multi Select.  For e.g. this query returns a single bytea column that contains a 13 Mb zip file.  pgAdmin takes 7 minutes to display the single row:

SQL Multi Select returns the same result set in 1 second.

 

Search function

In SQL Multi Select, you can search for a value in the result sets.  You can also use a regular expression to search for values.

pgAdmin is still great for administrative tasks, but as a query tool, SQL Multi Select may be better in some cases.  Download SQL Multi Select and give it a try now.  The 14-day trial will be automatically converted to the FREE Basic Edition once the trial period has elapsed.

Related blogs:

Exporting images from OLE Object fields

→ This article refers to SQL Blob Viewer, which has now been renamed to SQL Image Viewer.  The techniques described in this blog is still applicable, as the functionality of the product remains the same.  Only the name has changed.

If you use OLE Object fields in your Access or SQL Server databases to store images, you know it’s convenient to just be able to click on the item and have Windows open the image using the registered viewer on your computer.  However, it becomes difficult to extract the images because Access adds additional OLE data to the stored images, thus changing their original form.

Take a Windows bitmap image,

and store it in an OLE Object field in a SQL Server linked-table.

The original file was 2,002,182 bytes in size, but has increased to 2,002,298 bytes when stored in the OLE Object field.  Access has added 116 bytes to the image.

and that the data stored in the field is different from the original bitmap file.

How can we then export this image?  One way is to open the table using Access, then double click on the data, and Access should then open the stored image in the registered OLE server for that image type, in our example Microsoft Paint.

However, this is a tedious process if we need to export a lot of images.  SQL Blob Viewer and Access OLE Export are two applications that we developed to easily export images and other data stored in OLE Object fields.  Exporting the items using SQL Blob Viewer or Access OLE Export is just a matter of writing the appropriate SQL query or selecting the right table.  SQL Blob Viewer is for users who are comfortable writing queries and want more control over how images are exported, while Access OLE Export is for users who just want to be able to select a table or write a simple query and export the images as is.

SQL Blob Viewer also displays a preview of the image when we query the table:

So if you have a ton of images that you need to export from OLE Object fields, give SQL Blob Viewer or Access OLE Export a try.

There may be situations where both products are unable to identify embedded images correctly.  This may be because the registered OLE Server for that content type is not yet supported.  In these cases, send us (at support@yohz.com) a sample of the embedded data, and we will add support for that OLE server type.

Table and index sizes in DB Doc 4

Starting from DB Doc 4, table and index objects now have size properties.

For tables, the following size properties are available:

  • total size
  • table size (excludes indexes and toast sizes)
  • indexes size
  • toast size

For indexes, there is a single size property.  In all the report templates, the size properties are displayed.

In PDF and DOC reports:

 

In HTML and CHM reports:

DB Doc also displays the largest 20 tables and indexes by size in the template reports.  As usual, you can always customize the number of tables/indexes to display, and the details shown.

In PDF and DOC reports:

 

In HTML and CHM reports:

 

Take a look at the completes samples available here: http://www.yohz.com/dbdoc_samples.htm

Documenting your PostgreSQL database

PostgreSQL has a pretty nifty feature to add comments to database objects, using the COMMENT function.  You can pretty much add comments to almost any objects, like tables, view, foreign keys, constraints, columns, etc.

You add a comment using the COMMENT ON … function, then retrieve the comment using the obj_description function for most objects.  For columns, you’ll need to use the col_description function.

All this is rather tedious if you plan to add/remove comments for a lot of objects.  To make this task easier, we have recently released a free product, PgComment, downloadable here.  With PgComment, you just connect to your database and the application will list out all the comments attached to your tables, columns, indexes, views, sequences, domains, and functions.

You can then easily modify the comments, and apply them to the database.  Changes are cached, and sent to the server in a single batch, allowing for faster editing locally.  With the schema displayed in a hierarchical tree, you get an overview of what’s been commented and what has not.  This makes it so much easier to document your PostgreSQL database.

Another feature of PgComment is that you can view the properties of your database objects.  For tables, you can see details like the owner, total size, index size, estimated rows etc.

For indexes, you can see details like the index definition, index size, and various other properties.

Displaying these properties should help you write better comments for your objects.

If you want to generate PDF or HTML reports of your PostgreSQL database schema, take a look at DB Doc.  With DB Doc, you can generate customizable PDF and HTML schema reports in just a few clicks.