Restore basics: How to restore using T-SQL commands

SQL Server Enterprise Manager may be a quick and easy way to run backups and restores, but T-SQL commands offer much greater flexibility. Find out how to script your SQL Server backups or restores.

Another approach to SQL Server backup and restore, aside from using Enterprise Manager as discussed in my previous

tip, is to use T-SQL commands. Enterprise Manager can be a quick and easy way to run backups or restores, but T-SQL offers a lot more flexibility. With T-SQL you can script your backups or script to restore several backup files.

If you are not familiar with T-SQL commands or how to construct a restore statement, I offer this side-by-side comparison to show how commands are constructed versus options you may select using the Enterprise Manager GUI.

The basic restore syntax begins with one of two statements, RESTORE DATABASE or RESTORE LOG, depending on the type of restore you are running.

The base commands are as follows:

  • RESTORE DATABASE databaseName
  • RESTORE LOG databaseName

 
TABLE OF CONTENTS
   Enterprise Manager screen shots to perform a restore
   T-SQL commands to perform a restore
 
Enterprise Manager screen shots to perform a restore

Using Enterprise Manager to perform a restore works, but it is often much simpler to run the restore from a command line or to use a command to schedule the restore to run. If you are familiar with Enterprise Manager GUI tools, the following screen shots will show you how they equate to the command line syntax.


Screen Shot #1

Restore as database:

  • This is the same as the databaseName mentioned above.

Restore: Database

  • This option allows you to read the contents of the backup tables in the MSDB database.

Parameters

  • Show backups of database
  • First backup to restore
  • Point in time restore

Note: These options are not part of the restore command. They query backup tables in the MSDB database to show you which backups exist for a restore. This same information can be retrieved by querying the backup tables in MSDB.


Screen Shot #2

Restore as database:

  • This is the same as the databaseName mentioned above.

Restore: Filegroup or files

  • This option allows you to read the contents of the backup tables in the MSDB database for filegroup or file backups.

Parameters

  • Show backups of database
  • Select a subset of backup sets

Note: These options are not part of the restore command. These options query backup tables in the MSDB database to show you which backups exist for a restore. This same information can be retrieved by querying the backup tables in MSDB.


Screen Shot #3

Restore as database:

  • This is the same as the databaseName mentioned above.

Restore: From device

  • This option allows you to restore a database from a file or tape device. This is usually selected when you are restoring a backup from another server.

Parameters

  • Devices: This allows you to select a list of backup files from disk or tape
  • Restore backup set
    • Database – complete
      - RESTORE DATABASE
    • Database – differential
      - RESTORE DATABASE
    • Transaction log
      - RESTORE LOG
    • File or filegroup
      - RESTORE FILE
  • Read backup set information and add to backup history
    • This option runs the RESTORE VERIFYONLY command using the LOADHISTORY option.

Screen Shot #4

Options
Eject tapes (if any) after restoring each backup

  • UNLOAD
    Prompt before restoring each backup
  • No equivalent
    Force restore over existing database
  • REPLACE
    Restore database files as
  • MOVE 'logical_file_name' TO 'operating_system_file_name'

Recovery
Leave database operational. No additional transaction logs can be restored

  • RECOVERY
    Leave database nonoperational but able to restore additional transaction logs
  • NORECOVERY
    Leave database read only and able to restore additional transaction logs
  • STANDBY
    Undo file
  • This is the name of the undo file used with the STANDBY option

Screen Shot #5

Point in Time Restore

The point-in-time recovery appends this command to the last transaction log file restored.

  • STOPAT = '1/18/2006 6:23:36 PM'

 
T-SQL commands to perform a restore

As you can see from the following commands, using T-SQL to perform your restores is a pretty straightforward task. Becoming familiar with these command-line options allows you to easily write and reuse your code over and over again. By scripting out the restore commands you can easily write batch routines or dynamic code to perform routine tasks.

Simple database restore (restores one full backup file):

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'

Simple differential database restore (restores one full backup and one differential backup):

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'
WITH NORECOVERY

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Diff_20060307.BAK'

Note: A differential restore must follow a full database restore using the NORECOVERY option. Also, there is no difference in the way the command is constructed to specify a differential restore.

Simple transaction log restore (restores one full backup and one transaction log backup):

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'
WITH NORECOVERY

RESTORE LOG Northwind
FROM DISK = 'C:\Backups\Northwind_Log_20060307.BAK'

Note: A transaction log restore must follow a full database restore, a differential database restore or another transaction log restore using the NORECOVERY option after each previous restore command.

Simple filegroup or file restore:

RESTORE DATABASE Northwind
FILE = 'Northwind_data'
FROM DISK = 'C:\Backups\Northwind_File20060307'

Transaction log restore with a point-in-time restore:

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'
WITH NORECOVERY

RESTORE LOG Northwind
FROM DISK = 'C:\Backups\Northwind_Log_20060307.BAK'
WITH STOPAT = N'3/06/2006 6:23:36 PM'

Full, differential and transaction log restore:

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'
WITH NORECOVERY

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Diff_20060307.BAK'
WITH NORECOVERY

RESTORE LOG Northwind
FROM DISK = 'C:\Backups\Northwind_Log_20060307.BAK'

Full and two transaction log restores:

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'
WITH NORECOVERY

RESTORE LOG Northwind
FROM DISK = 'C:\Backups\Northwind_Log_20060307_1.BAK'
WITH NORECOVERY

RESTORE LOG Northwind
FROM DISK = 'C:\Backups\Northwind_Log_20060307_2.BAK'

Restore with different file names and/or file locations:

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backup\Northwind_Full_20060307.BAK'
WITH MOVE 'Northwind' TO 'C:\Data\northwind_log.ldf',
MOVE 'Northwind_log' TO 'C:\Log\northwind_data.mdf'

Restore with different database name:

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backup\Northwind_Full_20060307.BAK'
WITH REPLACE

Restore database and allow future restores to occur:

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backup\Northwind_Full_20060307.BAK'
WITH NORECOVERY

Restore database, make it read only and allow future restores to occur:

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'
WITH STANDBY = 'C:\Standby\UNDO_Northwind.DAT'

Restore contents of a backup file into the MSDB backup system tables:

RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'
WITH LOADHISTORY

Summary

As you can see, the restore commands are not that complicated. Once you are familiar with writing these commands, you will probably begin using Query Analyzer along with the appropriate command to perform your backups and restores. Whether you use Enterprise Manager, Query Analyzer, Maintenance Plans or third-party tools, all of these options use the same processing and will log when backups and restores occur into the backup and restore system tables in the MSDB database. The GUI tools are great when you need to do something once or very infrequently, but the command-line syntax is much more powerful and flexible when you need to do the same thing over and over again.

About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.

 


More information from SearchSQLServer.com


 

This was first published in March 2006

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close