Supported database objects

When backing up and restoring a database, the following describes which database objects and properties are supported.

Users and roles

SQribe does not support users and roles. The destination database for a restore operation needs to have its security model created independent of SQribe.

Schema collections

Schema collections in Security/Schemas are supported.

Tables

Table column types for SQL Server 2008 through 2016 are supported (including hierarchyid and geography). The following features are also supported:

  • Identities
  • Primary keys
  • Indexes
  • Foreign key constraints
  • Default values (types and explicit)
  • Collation
  • Computed columns
  • XML schema collection for xml types
  • Data (UTF8-encoded)

The following are NOT supported:

  • Memory optimized tables

Fulltext catalogs and indexes

Fulltext catalogs and indexes are supported, including type columns (for image and varbinary indexing).

Views

Views are supported, including columns with XML schema values.

User-defined functions (UDF)

User-defined functions in Programmability/Functions are supported.

User-defined data types (UDDT)

User-defined data types in Programmability/Types/User-Defined Data Types are supported with the following properties:

  • Default values

The following are NOT supported:

  • Rules (which Microsoft has deprecated)

User-defined table types (UDTT)

User-defined tables in Programmability/Types/User-Defined Table Types are supported with column types for SQL Server 2008 through 2016 (including hierarchyid and geography). The following additional features are supported:

  • Identities
  • Primary keys
  • Computed columns
  • XML schema collection for xml types

Default types

Default type objects in Programmability/Defaults are supported.

XML schema collections

XML schema collections in Programmability/Types/XML Schema Collections are supported.

Stored procedures (USP)

Stored procedures in Programmability/Stored Procedures are supported.

Extended properties

These are supported at the database level and for supported schema types (including columns and parameters).

Triggers

Database triggers in Programmability/Database Triggers and Tables are supported, including enabled/disabled properties.

How to backup a database

Backup mode will create T-SQL scripts for a SQL Server database, which can be restored at a later time.

Command syntax

sqribe backup [options]

Example backup command

sqribe backup /data_source:"server=localhost;database=AdventureWorks2017;user id=aw2017user;password='abracadabra';" /objects:"all" /output_path:"~/Desktop/sqribe-backup"

Backup options

The following command line arguments are available for backup operations:

/beep_on_completion:
Makes a beep sound when a SQribe operation completes (default: true).

/chunk_data:
Combine table data files into manageable files of a specific size (default: false)

/chunk_size_mb:
Maximum size of data script files/transactions in mb (5-1000; default: 10). Compressed data (see below) will make files smaller than the chunk size.

/compress_data:
Zip data insert script files to save disk space (default: false). NOTE: compressed data can only be restored using SQribe.

/confirm_start:
Require a keypress to begin a backup (default: true).

/console_dark_mode:
When enabled output is light in color; dark when disabled (default: true).

/data_blacklist:
Specify schema and table names in a comma-separated list to skip generating their data insert statements, for example "dbo.DatabaseLog,dbo.ErrorLog". Blacklisted items can end with a wildcard asterisk (*), for example "dbo.Error*".

/data_source:
Standard SQL Server connection string in quotation marks.

/hash:
Backup hash values are IDs (6 alphanumeric characters) for your backups and are generated for you. This option allows you to override that feature with your own hash value. This can be helpful when you want to, for example, deploy staging database schema changes to production. If both use the same hash, you could backup both separately and then copy the updated schema files into your production database backup, and then restore production.

/logging:
Log activity and exceptions for the most recent use of SQribe to file named sqribe-log.md in the sqribe data folder (in your user home folder) at ~/sqribe/ (default: true). Note: this will include private info, like connection strings with passwords.

/max_thread_count:
Maximum number of thread to allow (1-25; default: number of logical processors in the machine).

/objects:
Comma-separated list of objects to script (default is "all"):

all = all supported object types and data
schema = all supported object types but no data

— OR —

sc = schema collections
table = tables
pkc = table primary key constraints
index = table indexes
fkc = table foreign key constraints
cc = table check constraints
ftc = fulltext catalogs
view = views
udf = user-defined functions
usp = stored procedures
tr = triggers
uddt = user-defined data types
udtt = user-defined table types
dt = default types
xsc = XML schema collections
xp = extended properties
data = table data

/output_path:
Path for output script files (e.g. "~/Desktop/backups").

/suppress_start_warnings:
When enabled warning messages will be displayed for output script file overwrites (default: false).

/turbo_mode:
When enabled, table data will be scripted without concern for dependency order (like Microsoft's SqlPackage utility), yielding big speed improvements (default: false). This is not recommended for backing up live databases that have active writes as orphaned child rows can be created in the backup.

NOTE: Default backup behaviors can be changed by creating a file named "backup-defaults.json" in the sqribe data folder (in your user home folder) at ~/sqribe/. All properties except "data_source" and "mode" can be set here.

Troubleshooting

Backup and restore operations generate an activity log that contains more specific information about the last job run. In the case of SQL Server exceptions, you can look there to see the details of the error to help you troubleshoot. The file is in markdown format, so any standard text editor should be able to open it.

The file is in SQribe data folder (in your user home folder) at ~/sqribe/, and named sqribe-log.md. You can suppress the creation of this file with the /logging option mentioned above.