Sql Server Backup and Restore Backup

In this article, I will explain how to backup and restore database backups with SQL Server Management Studio. The backup process is to keep an instance of all data on the database against the risk of loss and corruption. This backed up data is then restored as needed to prevent possible data loss.

Backup Process with SQL Server Management Studio

You must know the types of SQL Server Backup before proceeding to backup. There are 3 different backup types on SQL Server.

  • Full Backup: As the name suggests, everything in the database is copied into the backup at the time of backup. With a full backup file available, the database can be restored to the date the backup was taken without the need for anything else.
  • Differential Backup: Backing up large databases with a lot of data with full backup continuously results in both long processing time and unnecessary disk usage. In such cases, it is only the process of backing up the changed parts.
  • Transaction Log Backup: Transaction is the log file that holds the information of each transaction performed in the related database. Backup covers only those records.

Since we talk about backup types, we can proceed to backup our database. Right-click on our database and click on Tasks–> Backup.

sqlserver-tasks-backup
Starting the Backup

The following window will open with settings related to backup. The window is divided into 3 main sections Source-Backup Settings-Destination.

sqlserver-backup-settings
Backup Settings Window

Source section settings:

  • Database: We choose which database we want to backup.
  • Recovery Model: The Recovery Model contains settings for how to log transaction logs. SQL Server has 3 different recovery model options. These are Full, Simple and Bulk Logged. You cannot change this section during the backup process.
  • Backup Type: I mentioned the backup types at the beginning of the topic. Let’s proceed by specifying that we want to receive a full backup.
  • Copy the only Backup: This is the setting whether or not to include the backup in the backup chain.
  • Backup Component: We determine whether the entire database or a Filegruop backup of the database is requested.

Backup Set partition settings:

  • Name: We give our backup a name.
  • Description: Use this section if you want to add a description of the backup.
  • The backup set will expire The date on which the backup is valid.

Destination section settings:

  • Backup To This section contains settings for where to store the backup. With the Disk and Tape options, you can set the file settings for where to copy the backup at the bottom.

In addition to these settings, you can also make more detailed adjustments from the Options section on the left of the window. I pass without mentioning that part.

After you have made the settings, press OK and if the error does not occur, you should display a message that the backup process was successful.

database-backup-completed
Backup Completed Successfully

If you did not change the destination path after the backup, your backup with the .bak extension will be available under C: / Program Files / Microsoft SQL Server / MSSQL11.MSSQLSERVER / MSSQL / Backup .

Restore Backup with SQL Server Management Studio

First of all, I would like to mention that when a backup file we have is restored, the existing database is overwritten unless otherwise specified.

Now let’s restore our database with the .bak file we just created. First of all, click on the Database–> Restore Database.

sqlserver-restoredatabase
Starting the Restore

The pop-up window contains the settings for the Restore operation. Window; It is divided into 3 sections called Source-Destination-Restore Plan. I don’t think there’s any need to explain these chapters. We select the backup from Source. From Destination, we select the database to which we will load the backup. Restore Plan section shows the list of backups and information we have taken if you have more than one backup you choose which backup you want to install. When you press the arrow key, the database restore process starts.

sqlserver-restoredatabase-settings
Restore Settings Window

If you do not receive an error when the process is completed, a message will be displayed indicating that the transaction was successful as follows.

sqlserver-restore-completed
Restore Successfully Completed

The SQL Server backup and restore process is much more complex than the one described above. This article describes the most basic backup operations.