A database developer and Database Administrator should be familiar with these methods.

If you don't know how to restore a SQL Server Backup then you need to look at this article. You can restore a full, differential or transaction log backup.

Note: When you are restoring a database the user cannot access the database. It will show restoring mode.

You can restore a SQL Server BACKUP with using one of multiple methods using TSQL as in the following:

  • Restore a full database
  • Restore the backup using a backup drive
  • Restore full and differential database backups
  • Restore a transaction log backup
  • Restore multiple transaction log backups from same backup file
  • Restore a database and move files
  • Copying a database using Backup and Restore
  • Copying a database using Backup and Restore with Backup Drive
  • Restoring using TAPE syntax

Let's create a Test scenario.

  1. --Create Test DB.
  2. USE [Master]
  3. GO
  4. CREATE DATABASE RestoreDB
  5. GO
In my previous article, I already discussed How to take database backup with multiple method using Transact-SQL. Let's use the same for restoring a database backup.

Restoring a Full backup

I will restore a full backup from the database "RestoreDB" using disk with/without (NOREVERY) and Backup Device.

For example:

  1. 1.--Restore full backup using disk
  2. USE [Master]
  3. GO
  4. RESTORE DATABASE RestoreDB FROM DISK = 'D:\backup\NewBackup\RestoreDB.BAK'
  5. GO
  1. 2.--Restore full backup using disk with NORECOVERY
  2. USE [Master]
  3. GO
  4. RESTORE DATABASE RestoreDB FROM DISK = 'D:\backup\NewBackup\RestoreDB.BAK'
  5. WITH NORECOVERY
  6. GO
  7. --Recover the database:
  8. RESTORE DATABASE RestoreDB WITH RECOVERY;
  9. GO
Restore Backup using Backup Drive

You can restore backup by Logical Backup drive that have stored multiple backup.

  1. --Restore full backup using backup device
  3. RESTORE DATABASE RestoreDB
  4. FROM [DBBackupDevice]
  5. GO

Restoring full and differential database backups

Note: differential backup will be restored after full backup restored.

  1. USE [Master]
  2. GO
  3. --Restore full backup using disk with NORECOVERY
  4. RESTORE DATABASE RestoreDB
  5. FROM DISK = 'D:\backup\NewBackup\RestoreDB.BAK'
  6. WITH NORECOVERY
  8. GO
  9. --Restore differential backup using disk with RECOVERY
  10. RESTORE DATABASE RestoreDB
  11. FROM DISK = 'D:\backup\NewBackup\RestoreDB.DIF'
  12. WITH RECOVERY;
  14. GO

Restore a transaction log backup

Now you can restore the transaction log after a Full and differential backup restore.

  1. USE [master]
  2. GO
  3. RESTORE LOG RestoreDB FROM DISK = 'D:\backup\NewBackup\RestoreDB.TRN'
  4. WITH NORECOVERY;
  5. GO
Restore multiple transaction log backups from the same backup file

You can use the same backup file, RestoreDB.TRN, to write all of our transaction log backups.

Restoring a database and move files

Now I will move MDF and LDF files to new files; you can see below:

  1. --Moving full database and transaction log
  2. RESTORE DATABASE RestoreDB
  3. FROM [DBBackupDevice] WITH NORECOVERY,
  4. MOVE 'RestoreDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RestoreDB_New.mdf',
  5. MOVE 'RestoreDB_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RestoreDB_log_New.ldf';
  6. RESTORE LOG RestoreDB
  7. FROM [DBBackupDevice] WITH RECOVERY;

Copying a database using BACKUP and RESTORE

You can make a copy of a database using backup and restore, for example:

  1. --copy database using Full backup and restore
  2. ESTORE DATABASE RestoreDB_COPY
  3. FROM DISK = 'D:\backup\NewBackup\RestoreDB.BAK'
  4. ITH
  5. MOVE 'RestoreDB' TO 'C:\RestoreDB.mdf',
  6. MOVE 'RestoreDB_log' TO 'C:\RestoreDB_log.ldf',
  7. REPLACE, NORECOVERY;
  9. GO
  10. RESTORE DATABASE RestoreDB_COPY
  11. WITH RECOVERY;

Copying a database using BACKUP and Restore with Backup Drive

You can make a copy of a database using backup and restore with a logical Backup Drive as in the following:

  1. --copy database using Full backup and restore with backup device
  3. RESTORE DATABASE RestoreDB_NewCOPY
  4. FROM [DBBackupDevice]
  5. WITH
  6. MOVE 'RestoreDB' TO 'C:\RestoreDB.mdf',
  7. MOVE 'RestoreDB_log' TO 'C:\RestoreDB_log.ldf'
  8. GO

Restoring using Tape syntax

For must be Tape for Restores a full database backup from a TAPE backup device.

  1. -- Restore with Tape device
  2. USE [master]
  3. RESTORE DATABASE RestoreDB
  4. FROM TAPE = '\\.\tape0';

Summary

This article describes how to restore a SQL Server backup with multiple methods using Transact-SQL and how it will be used in SQL Server.