Previous Next

Best Way to Back Up an SQL Server Database

Overview

SQL Server backup is meant for protecting data stored in SQL Server database from critical loss due to hardware failures, network intrusions, human errors, etc.

With dbForge Studio for SQL Server the backup procedure can be performed in two ways:

  • with dbForge Backup wizard
  • with dbFroge Generate Scripts wizard
In this article we will consider both methods and determine differences between them in pursuit to distinguish the best and most optimal backup method.

Backup with dbForge Backup Wizard

1. In Database Explorer, right-click the required database, point to Tasks and select Back Up... from the shortcut menu.

Backup Database Command

2. The Backup wizard opens.

3. On the General tab of the wizard:

  • specify connection and the required database that you going to make a backup from;
  • select a connection or click Edit or New to edit the current connection or create a new one;
  • click Add to specify the backup file path on the server;
  • click Next.
Backup Wizard - Backup

4. On the Mediaoptions tab, you can modify general media options, including Overwrite Media, Reliability and Transaction Log. Click Next.

Backup Wizard - Mediaoptions

5. On the Backup options tab, you can set a description for a backup file, correct a file name generated automatically, or set the backup expiration date. Click Back Up.

Backup Wizard - Options

6. When the backup is already in progress you can interrupt it by clicking the Cancel button.

Backup Wizard - Progress

7. The Finish tab displays whether the backup process has been finished successfully. Click Finish to close the Backup wizard window and complete the backing up.

Backup Wizard - Finish

To restore a database:

1. In Database Explorer, right-click the required database, point to Tasks and select Restore... from the shortcut menu.

Restore Command

2. The Restore wizard opens with the latest backup file. Follow its steps to complete the restore.

Backup with dbForge Generate Scripts Wizard

1. In Database Explorer, right-click the database, point to Tasks and select "Generate Scripts..." from the shortcut menu.

Generate Scripts Command

2. The Generate Scripts wizard opens.

3. On the General tab of the wizard, set Connection, Database, File path and File Name. Click Next.

Scripts Wizard - General

4. On the Script content tab, specify what you what to back up — structure, data or both. You can also select particular database objects you want to back up. Click Next.

Scripts Wizard - Script content

5. (Optional) On the Options tab, select additional options for the script creation, if required. Click Next.

Scripts Wizard - Options

6. On the Errors Handling tab, specify how the errors should be treated. Click Generate.

Scripts Wizard - Errors Handling

7. When the backup is already in progress you can interrupt it by clicking the Cancel button.

Scripts Wizard - Progress

8. The Finish tab displays whether the backup process has been finished successfully. Click Finish to close the Backup wizard window and to complete the backup.

Scripts Wizard - Finish

To restore the backed up database:

1. Create a new database: in Database Explorer, right-click the connection and select New Database.

Creat New Database Command

2. Open the generated SQL script file.

3. On the SQL toolbar, select the database you want to restore and click Execute.

Restore Script

Summary

dbForge Studio for SQL Server enables to perform a coplete database backup in two ways — with dbForge Backup wizard and with dbForge Generate Scripts wizard. To sum up, we would like to list pros and cons of each backup method.

Backup with dbForge Backup Wizard

Pros:

  • the fastet way to backup a database;
  • allows to backup databases of any size;
  • generates a single backup file;
  • a standard SQL Server command;
  • incremental backup is possible.

Cons:

  • does not allow to backup separate files; only the entire database can be backed up;
  • impossible to backup a database of the lower version of SQL Server and restore it to the higher version of SQL Server;
  • impossible to edit a bakcup file with standard tools.

Backup with dbForge Generate Scripts Wizard

Pros:

  • allows to backup any database object, as well as to choose what to backup — structure or data;
  • allows to open and edit a backup file in accordance with one's own needs;
  • allows to backup a database on the lover version of SQL Server and restore it to the higher version of SQL Server;
  • allows to save arcived backup files;
  • the backup file can be executed from any SQL Server client tool, e.g. SSMS, SQLCMD, etc.

Cons:

  • very slow way to get a result file;
  • may cause memory overload while backing up large amount of data;
  • does not allow to encrypt data.