Best Way to Back Up an SQL Server Database

With dbForge Studio for SQL Server the backup procedure can be performed with:

• dbForge Backup Wizard
• 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. Moreover, we are going to describe the ways to schedule and automate SQL server backups.

The basic BACKUP DATABASE statement allows to create a full database backup in SQL Server using the following syntax:

• BACKUP DATABASE databasename
• TO DISK = 'filepath';

Make sure to save the backup on a different disk from the one where the database is stored. This way you will have your data intact in case of disk crash. In order to restore a database successfully in the future, it is also important to check if all the files were saved correctly.

Types of SQL Server backups

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. Backups are useful in a variety of areas, thus, there are several types of them to suit any situation:

Create a Full Database Backup using dbForge Backup Wizard

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

2. The Backup wizard opens.

3. On the General tab of the wizard:

• Specify the 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.

4. On the Media Options tab, you can modify general media options, including Overwrite Media, Reliability and Transaction Log. Once done, click Next.

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

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

7. The Finish tab displays the backup completion status: whether the process was successful or not. Click Finish to close the Backup wizard window and complete the backing up.

To restore a database:

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

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

Create a Full Database Backup using dbForge Generate Scripts Wizard

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

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

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

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

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

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

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.

To restore a SQL database from a backup:

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

2. Open the generated SQL script file.

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

How to Create Automated Backup for SQL Server Database

Unfortunately, dataloss does happen from time to time regardless of how hard one tries to avoid it. To be on the safe side, it is always better to keep a fresh backup of your database. Of course, you can do it manually. However, to save your time, you can automate SQL server backup creation and have your back covered for good. For that, you can use dbForge Studio for SQL Server, which allows you to fully control and customize the whole process.

Automatic backup creation presupposes:

1. Creating a SRV schema in the database.
2. Including the desired names into a table.
3. Creating .bak/.trn files.
4. Saving stored precedures.
5. Invoking the stored procedures according to a particular schedule via the agent tasks or any other third-party tools.

For more information regarding SQL server backup automation and scheduling, visit our blog.

Summary

dbForge Studio for SQL Server enables to perform a complete 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.

Create a Full Database Backup using dbForge Backup Wizard

• Pros:
  • the fastest 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:
  • impossible 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 backup file with standard tools

Create a Full Database Backup using 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 lower version of SQL Server and restore it to the higher version of SQL Server
  • allows to save archived 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

View more useful features

• Copy Database
• Data Import and Export