How It Works

⌘K
  2. Home
  4. Docs
  6. How It Works
  8. How to Backup/Restore Dat...
  10. SQL Server

SQL Server

SQLBackupAndFTP allows you to set a connection to MSSQL Server via one of the options: Microsoft SQL Server (local) or Microsoft SQL Server (remote). This primarily affects how SQLBackupAndFTP performs backups.

Microsoft SQL Server (local)

The backup is created via the T-SQL command BACKUP DATABASE:

BACKUP DATABASE [db_name] TO DISK = "path\to\backup\folder"

Differential backups are created with the following command:

BACKUP DATABASE [db_name] TO DISK = "path\to\backup\folder" with differential

Transaction log backups are created using the following command:

BACKUP LOG [db_name] TO DISK = "path\to\backup\folder"

The restore process is performed using the command RESTORE DATABASE:

RESTORE DATABASE [db_name] FROM DISK = "path\to\backup"
You can view the command used for backup or restore by clicking Tools – Advanced Log… and enabling the Trace level. Next, run a job, and search for the substring BACKUP DATABASEor RESTORE DATABASEthere.

Microsoft SQL Server (remote)

For remote connection, SQLBackupAndFTP creates a logical backup in the form of a T-SQL script using SMO libraries. The script file will contain commands such as CREATE DATABASE, CREATE TABLE and INSERT INTO TABLE.

This operation is similar to the operation that can be performed through SSMS: right-click on the database, then click Tasks – Generate Scripts…, with the Schema and Data option selected.

Unlike the first method, this method has several drawbacks: backup creation takes longer, significantly increases the load on the database, and there may be issues with scripting certain values (such as BLOB fields). We do not recommend using this type of connection.