This article shows several ways you can restore an SQL Server database backup. In particular, we will consider how to restore a database

1. Using T-SQL commands
2. Using SQL Server Management Studio
3. Using SQLBackupAndFTP application

How to restore an SQL server database from backup using T-SQL commands

The RESTORE DATABASE command is the most basic and universal way to restore SQL Server backups since T-SQL commands work everywhere whether you type them in SQL Server Management Studio, execute via sqlcmd utility or run from your program. Let’s review what commands are used to restore three types of backup: full, differential and transaction log backups.

Restore full SQL Server database backup

Full backups contain all information necessary to restore your database to the point in time when the backup process had finished. The backup will overwrite your database if one exists or produce a new SQL Server database. Let’s say your full backup is stored in D:\Adventureworks_full.bak and you want to restore it to the Adventureworks database. You need to execute the following commands:

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

If you are going to continue with restoring differential or transaction log backups after that, you need to add the NORECOVERY option. This will leave the backup process in a restoring state and allow you to restore extra differential or transaction log backups.

RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_full.bak' WITH NORECOVERY

Restore differential SQL Server database backup

Differential backups contain changes that took place in the database since the last full backup. The last differential backup accumulates all the changes so you don’t need all previous differential backups to restore a database, just the last one. Before restoring the differential backup, you need to restore the last full backup first with the NORECOVERY option and then the last differential backup with the RECOVERY option:

RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_full.bak' WITH NORECOVERY
GO
RESTORE DATABASE Adventureworks FROM DISK = 'D:\AdventureWorks_diff.dif' WITH RECOVERY
GO

Restore transaction log SQL Server database backup

Transaction log backups contain all transactions that took place between the last transaction log backup (or the first full backup) and the moment when the backup process had finished. You have to restore all transaction log backups made after the last differential backup in the same sequence they were made. And, obviously, log backups are restored after you’re done with full and differential backups (read more about point-in-time recovery):

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

How to restore a database from backup using SQL Server Management Studio

If you have SQL Server Management Studio installed, you can restore database backup using its interface alone. Just follow the instructions:

1. Connect to your SQL Server, right-click on the “Databases” directory, and choose “Restore Database”

2. Click the button beneath the “Source” section next to “Device”
3. In the “Select backup device” window press “Add”
4. Select the backup file or files (.bak) you are going to restore, then click “OK”
5. In the “Restore Database” window specify the database’s name you will restore and click “OK” to start

Your SQL Server database is restored.

How to restore an SQL Server database using SQLBackupAndFTP

If you use SQLBackupAndFTP to create SQL Server backups then you can restore them right from the history pane:

To restore on the same server where the backups were taken, in the “History & restore” section of SQLBackupAndFTP select the backup you want to restore, click the dots button on that line, select “Restore from Backup…” and follow the steps.

The good thing here is that you can choose any backup of any type (full, differential or log) and all other necessary backups will be restored automatically.

If you need to restore other backups that were made via SQLBackupAndFTP, or Standard T-SQL Command “CREATE BACKUP”, or SQL script or a .bacpac file then, with the following steps, you can create a “Restore Job” to do it:

1. Choose a place where your backups are stored
2. Select a database to restore
3. Connect to the Server to which you need to restore the backups
4. Click “Run Now”

Also, you can set the email notification option, set a password if your backups are encrypted and specify a temporary folder.

The only requirement is that the name of a backup should comply with the following format:

{DatabaseName}{YYYY}{MM}{DD}{hh}{mm}[log|diff].[sql|bacpac|7z|zip|bak]

Restore SQL Server databases via the command line

Launching a restoration from a script allows you to use it in automation scenarios. There are two ways to launch the SQL Server restoration from a .bat or .ps1 script.

Restore database via sqlcmd

The sqlcmd utility allows you to execute any T-SQL commands; it is installed with SQL Server.

If you backup a local server using Windows authentication, then use the following command:

sqlcmd -Q "RESTORE DATABASE [database_name] FROM DISK='C:\Backup\backup_file.bak' WITH RECOVERY

Otherwise, specify server’s name, login, and password:

sqlcmd -S server_name -U username -P password -Q "RESTORE DATABASE [database_name] FROM DISK='C:\Backup\backup_file.bak' WITH RECOVERY"

Restore database via PowerShell

PowerShell has a special module for working with SQL Server called SQLServer. To use it, you need to install the module using the following command:

Install-Module -Name SqlServer

After installing the module, you can use the Restore-SqlDatabase command to restore the database:

Restore-SqlDatabase -ServerInstance '.' -Database [database-name] -BackupFile 'C:\Backup\backup_file.bak' -ReplaceDatabase