Restore SQL Database From Backup

In this blog post let’s discuss one of the main parts in the life of every DBA this is how to restore SQL database from backup. The easiest way to restore your SQL Server database from the full backup is to use the simple tool – One-Click Restore. You can install this tool on your server or computer and restore your database automatically promptly. But below let’s discuss the other ways of database restoration.

Restore SQL Database from Backup using T-SQL

In this part of our article, we are going to show how to restore SQL Database from backup using T-SQL Commands.

Restore a full backup

Use the following T-SQL Command to restore the full backup of your SQL Server database. This backup will overwrite your database if such is exist or create a new SQL Server database.

RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_full.BAK'

After you have restored your full database backup you can restore a differential or transaction log backups. If you need to do it, restore your full backup using NORECOVERY option. This option leaves a backup on restoring state and allows you to restore additional differential or transaction log backups.

RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_full.BAK' WITH NORECOVERY
 Restore a differential backup

The T-SQL Command to restore the differential backup is the same as the Command to restore a full backup. All you need to do is don’t forget to add NORECOVERY option.

RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_full.BAK' WITH NORECOVERY
GO
RESTORE DATABASE Adventureworks FROM DISK = 'D:\AdventureWorks_diff.DIF'
GO
Restore a transaction log backup

If you need to restore a transaction log backup, please remember, that your SQL Server database should be in the restoring state. That means that it is necessary to restore a full backup and a differential backup, if it needed, before.

RESTORE LOG Adventureworks FROM DISK = 'D:\Adventureworks_log.TRN'
Restore multiple transaction log files using NORECOVERY option

As we covered before you need to add NORECOVERY option to leave the database in a restoring state. Below, you can find the example how to restore your SQL Server database using the following restore scenarios:

RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_full.BAK' WITH NORECOVERY
GO
RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_diff.DIF' WITH NORECOVERY
GO
RESTORE LOG Adventureworks FROM DISK = 'D:\Adventureworks_log1.TRN' WITH NORECOVERY
GO
RESTORE LOG Adventureworks FROM DISK = 'D:\Adventureworks_log2.TRN' WITH RECOVERY
GO

Restore SQL Database from Backup using SQL Server Management Studio (SSMS)

Below we will cover how to restore SQL database from backup with the help of SSMS (SQL Server Management Studio).

  1. Login to your SQL Server and right-click on the “Database” folder and select “Restore Database”. Restore SQL Database from Backup using SQL Server Management Studio (SSMS)
  2. Press the button under the “Source” section next to “Device”. Restore SQL Database from Backup using SQL Server Management Studio (SSMS)
  3. In the “Select backup device” press “Add”. Restore SQL Database from Backup using SQL Server Management Studio (SSMS)
  4. Select the backup file or files (.bak) you are going to restore.
  5. In the “Restore Database” window specify the database’s name you are going to restore and press “Ok” to start. Restore SQL Database from Backup using SQL Server Management Studio (SSMS)

That’s it. Your SQL Server database is restored!Restore SQL Database from Backup using SQL Server Management Studio (SSMS)

Restore SQL Database from Backup using One-Click Restore

If you need promptly restore SQL database from backup (full backup), then the easiest way is to use simple tool One-Click Restore. All you need to do is to select a .bak or .zip backup file and select a database where you want to restore it.Restore SQL Database from Backup using One-Click Restore

[Total: 1    Average: 5/5]

Leave a Reply

Your email address will not be published.