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"

Data file paths during restore (MDF and LDF)

SqlBackupAndFTP determines paths to database files (MDF and LDF) during restore using the following priorities:

  • If a database with that name already exists and has a file with the same logical name — the existing file of the database being replaced is used.
  • If the files specified in the .bak file can be created — the paths from the backup file are used (i.e. when the drive exists and SQL Server has access to that directory).
  • If no database with that name exists — new paths are generated in the SQL Server data directory.
  • If no database with that name exists but a file with that name already exists in the SQL Server data directory — a file with a unique name is created in the SQL Server data directory.

 

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.