Post
Ask Question

Restore a SQL Server BACKUP Using Transact-SQL

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   
  2.   
  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  
  7.   
  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;  
  13.   
  14. GO  
  15.    
 

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;  
  8.      
  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  
  2.   
  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.


Similar Articles