Create Full SQL Server Database Backup with dbForge Studio for SQL Server

February 8th, 2021

The backup process is an important link in the chain of successful database administration and robust data protection. In this article, we are going to apply a comprehensive solution from dbForge Studio for SQL Server that allows quick and efficient database backup and restore.

This is the fourth part in the series of articles regarding the database backup and restore process in SQL Server databases. Let’s look back on the topics that we have already discussed in the previous articles:

  1. The basics of SQL Server backup.
  2. The implementation and automation of the SQL Server backup process.
  3. Restoring a SQL Server database backup via T-SQL script and via SSMS.

This time, let’s review one of the most viable solutions that provides quick database backup and restore. dbForge Studio for SQL Server delivers multiple features that help organize smooth SQL Server database administration. This solution is an independent IDE with a convenient user interface that accelerates database development and administration without using additional tools and plugins. Among other functional capabilities, the Studio has an advanced Backup Wizard designed to automate and facilitate the backup and restore process.

Administration Capabilities Overview

To proceed with the administration features, let’s switch to the Administration tab of dbForge Studio for SQL Server:

The Administration tab within dbForge Studio for SQL Server

Img. 1. Administration functions

You can find the following options there:

1. Manage Server Security – the roles and users manager that works with their access levels:

Adding a new user within Security Manager

Img. 2. Adding a new user

2. Attach database – the database attach option:

 The database attach option within dbForge Studio for SQL Server

Img. 3. Selecting the database for attach

3. Backup – the backup function;

4. Restore – the restore from backup function;

5. Monitor – real-time monitoring of the database and MS SQL Server instance status:

The Monitor functionality within SQL Server Studio

Img. 4. The status monitor

6. Generate Script – scripts generation that is supposed to create a database and fill tables with data according to the selected objects and object types:

The generation of database creation scripts

Img. 5. The database creation script generation

7. Copy Database – copying of the selected databases from one server to another:

Selecting the database to copy within dbForge Studio for SQL Server

Img. 6. Copying of the chosen databases

8. Profile Server Events – profiling of queries and events:

Queries and events profiling in a SQL Server database tool

Img. 7. Queries and events profiling

9. Manage Index Fragmentation – the index fragmentation optimization manager:

Index Fragmentation options in the SQL Server database IDE

Img. 8. Index fragmentation optimization manager

Further, we are going to examine the processes of making a backup and restoring from backup in detail. First, let’s review the backup procedure.

Creating a backup with dbForge Studio for SQL Server

1. To perform a backup, go to the Administration tab and click Backup:

The Administration tab within dbForge Studio for SQL Server

Img. 9. The Administration tab main window

2. On the General tab, we can choose the following options:

The General tab settings within dbForge Studio for SQL Server

Img. 10. The General tab settings

  1. Connection – configure the connection to the MS SQL Server instance.
  2. Database – select the database for a backup.
  3. Backup type – select the backup type (full, differential, or transaction log).
  4. Backup component – choose whether you want to back up an entire database or its particular files or filegroups.
  5. Copy-only backup – create a backup for copying only.
  6. Backup to disc – set the backup destination.

3. On the Media options tab, configure the backup parameters. Note that we select all options in the Reliability section:

The Media Options tab settings in dbForge Studio

Img. 11. The Media Options tab settings in dbForge Studio for SQL Server

4. On the Backup options tab, select the backup set and encryption options:

The Backup Options tab settings in dbForge Studio for SQL Server

Img. 12. The Backup Options tab settings in dbForge Studio for SQL Server

Note that we can generate a backup script by clicking the Script button. Besides, we can launch the backup process by clicking BackUp or OK.

Below is an example of the generated script:

BACKUP DATABASE JobEmplDB
TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Log\JobEmplDB_Log_backup_2020_07_20_00_24_43.trn' WITH DESCRIPTION = N'Full Database backup', NAME = N'JobEmplDB-Full Database backup', NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 1, CHECKSUM, CONTINUE_AFTER_ERROR
GO
DECLARE @backupSetId AS INT
SELECT
  @backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
    MAX(backup_set_id)
  FROM msdb..backupset
  WHERE database_name = N'JobEmplDB')
IF @backupSetId IS NULL
BEGIN
  RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1)
END
RESTORE VERIFYONLY FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Log\JobEmplDB_Log_backup_2020_07_20_00_24_43.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

As for the differential backups and transaction log backups, you can perform them in a similar way.

5. We start the backup process by clicking the BackUp button:

Initiating the backup process in dbForge Studio for SQL Server

Img. 13. Backup process launching in dbForge Studio for SQL Server

During the backup process, we can track the progress on the Progress tab. Upon the completion of the backup process, the Finish tab shows the following summary:

The summary of a successful backup

Img. 14. The summary of the backup process

Restoring a backup using dbForge Studio for SQL Server

1. To restore a backup, go to the Administration tab and select Restore:

The Restore functionality on the Administration tab of dbForge Studio for SQL Server

Img. 15. The Administration tab main window

2. On the Source and Target tab, we can select the following settings:

The Source and Target tab settings for SQL Server database restore

Img. 16. The Source and Target tab settings

  1. Connection – configure the connection to the MS SQL Server instance.
  2. Source for restore – choose the database or a backup file for restore.
  3. Destination to restore – configure the database name and the exact time for restore.

3. On the Files tab, we configure the destination to restore each database file:

The Files tab within dbForge Studio for SQL Server

Img. 17. The Files tab settings

4. On the Options tab, we need to configure the restore parameters:

The Options tab settings in the IDE for SQL Server

Img. 18. The Options tab settings in dbForge Studio for SQL Server

Note that it is possible to generate the restore script by clicking the Script button. Also, we can launch the restore process by clicking Restore or OK.

Below is the example of the generated script:

USE master
RESTORE DATABASE JobEmplDB2
FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Log\JobEmplDB_Log_backup_2020_07_20_00_24_43.trn' WITH REPLACE,
     FILE = 2,
     MOVE N'JobEmplDB' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\JobEmplDB2.mdf',
     MOVE N'JobEmplDB_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\JobEmplDB2_log.ldf',
     STATS = 1;

Now, we launch the restore process by clicking the Restore button:

Launching the restore process within dbForge Studio for SQL Server

Img. 19. Restore process launch

During the restore process, we see the progress bar on the Restoring tab. Upon the process completion, the Operation complete tab shows the summary:

The successful database restore process in dbForge Studio for SQL Server

Img. 20. The restore information summary

Conclusion

To sum up, in the current article, we have reviewed the implementation of the SQL Server database backup and restore procedures using dbForge Studio for SQL Server. Follow this straightforward guide to protect your database and reap a whole spectrum of additional benefits provided by the tool.

Latest posts by Evgeniy Gribkov (see all)

Leave a Comment