Preface

This article will use the built-in MSSQL methods to:

• Backup MSSQL
  
  • Full MSSQL Backup
  • Partial backup without the File Tracking and Server Log information
• Restore MSSQL
  
  • Restore from a full MSSQL backup
  • Restore from a partial backup without File Tracking and Server Log information

Microsoft SQL Server backups should always follow the Microsoft-suggested procedures for the best and fastest way of taking backups. These procedures are fully documented by Microsoft and the database administrator is advised to familiarize themseleves with the information on that page before approaching the backup of the ST database.

Before executing any of the below steps, you MUST stop all SecureTransport services. Failure to do so will result in a corrupted backup.

Backup

Full MSSQL database backup

As MSSQL database admin execute the following statement in the SQL Management Studio([schemaName] is the database user SecureTransport is running with):

ALTER DATABASE [schemaName] SET RECOVERY SIMPLE

Open SQL Server Management Studio and connect to the SQL server.

Expand Databases.

Right-click on the ST database, then select Tasks → Back up.

In the Back Up Database window verify that:

• The Database field contains the name of the database you want to back up
• Backup Type is set to Full
• The Destination of the backup file is a valid location

When ready press OK

Partial MSSQL backup without File Tracking and Server log

1. Export the Database schema

Open SQL Server Management Studio and connect to the SQL server.

Expand Databases.

Right-click on the ST database, then select Tasks > Generate Script

In the Generate and Publish Scripts window, select Choose Object then select the Select specific database objects option and then mark the Tables and Stored Procedures checkboxes.

Click Next

In the Select Scripting Options window set the destination where you would like to export the database schema

Click on the Advanced button and select the exact same options as on the screen-shot below

When ready, press Finish

This will save the DB schema to an .sql file

2. Export the data

Open SQL Server Management Studio and connect to the SQL server.

Expand Databases.

Right-click on the ST database, then select Tasks > Generate Script

In the Generate and Publish Scripts window, select Choose Object then select the Select specific database objects option and then mark the Tables and Stored Procedures checkboxes.

Expand Tables

Un-check the below tables:

logging_event 
logging_event_temp
logging_event_exception
logging_event_exception_temp
logging_event_property
logging_event_property_temp
SubtransmissionStatus
SubtransmissionStatus_temp
TransferData
TransferData_temp
TransferDetails
TransferDetails_temp
TransferResubmitData
TransferResubmitData_temp
TransferProtocolCommands
TransferProtocolCommands_temp

Click Next

In the Select Scripting Options window set the destination where you would like to export the database data

Click on the Advanced button and select the exact same options as on the screen-shot below

When ready, press Finish

This will save the DB data to an .sql file

Restore

Restore from a full MSSQL database backup

Open SQL Server Management Studio and connect to the SQL server.

In case the original ST database was dropped, right click on Databases → Restore Database

If the original ST database still exists, right click on the ST database, then select Tasks → Restore → Database

In the Restore Database window, select to restore from Device and browse to the previous DB backup file

In case the original ST database was not dropped, you will also need to select the Overwrite Existing database option in Options tab

Press OK and wait until the restore is compete

Restore from a partial MSSQL backup without File Tracking and Server log

This backup can only be restored over an empty ST database!

Drop the existing ST database and user and recreate them per the requirements in the corresponding ST version Install Guide, chapter "Requirements for Microsoft SQL Server databases". You can find attached to this KB examples for dropping and recreating database and login.

Import the DB Schema: run the exported schema file from the Export the Database schema step above in the SQL Query Analyzer

Import the data: run the exported schema file from the Export the Data step above in the SQL Query Analyzer