The latest version of our Query-Level Export/Backup App, "
SQL Table Zip", has a brand new feature - A stand-alone console version, allowing you to Import & Export any query from your command line / powershell / directly from SSMS.
Here is a simple end-to-end walkthrough with the console version:
After installing the latest version, your destination folder has a new file named SqlTableZipConsole.exe
To make it easy to access, the setup process, by default, has a new checkbox for adding the installation folder to your environment variables path. This means that you can type SqlTableZipConsole (or SqlTableZip) from anywhere in your command line. An indication that it works would be to simply open a 'cmd' window and type in SqlTableZipConsole like in this screenshot:
From this point on, we'll be using SSMS for the demo, although it can be done anywhere.
The basic argument that SqlTableZipConsole takes is /ConfigFile:\path\to\MyConfigFile.xml
Here's a sample BACKUP config file, notice the <action> tag and the following properties:
\temp\demo\backup_config_demo.xml
<?xml version="1.0" encoding="utf-8" ?>
<config>
<Action>Backup</Action>
<Query>SELECT * FROM Production.WorkOrder</Query>
<BackupFile>c:\temp\ProdWorkOrder.sqz</BackupFile>
<BackupConnectionString>Data Source=127.0.0.1;Initial Catalog=AdventureWorks2014;Trusted_Connection=True</BackupConnectionString>
</config>
Next, let's see how we run everything inside SQL Server Management Studio (SSMS)
First thing - switch SSMS to SQLCMD mode:
Next, let's run a simple backup command based on the configuration file above. In SSMS the syntax will look like this:
Let's explain the results: The query result is now stored in ProdWorkOrder.sqz (SqlTableZip format) and has been compressed from the original ~16mb to ~0.5mb! That's a lot.
Now let's see how we restore this file back to a table. For the demo we'll restore the file to a different database under a different table name.
Here's the Restore's configuration file:
\temp\demo\restore_config_demo.xml
<?xml version="1.0" encoding="utf-8" ?>
<config>
<Action>Restore</Action>
<RestoreFile>c:\temp\ProdWorkOrder.sqz</RestoreFile>
<RestoreTableName>MyNewTable</RestoreTableName>
<RestoreConnectionString>Data Source=127.0.0.1;Initial Catalog=Sandbox;Trusted_Connection=True</RestoreConnectionString>
</config>
Note the different table name provided <RestoreTableName> as well as the different database name in the <RestoreConnectionString> tag.
Now, we'll point SqlTableZipConsole to this configuration file and run:
That's the entire end-to-end operation in a nutshell! Here's a simple select from the new created table:
One final note:
While SqlTableZip is not a free software, the
restore operation is. This means that even with no purchased license, restoring a previously backed-up files is always possible.
