Restore Database

Restoring a database copies data from a backup file into a new or existing database. This operation is essential for disaster recovery, migration, or creating test environments.

When to Use

Recovering from hardware failure.
Rolling back to a known good state after a bad deployment.
Cloning a production database for development.

Basic Syntax

RESTORE DATABASE [database_name]
FROM DISK = 'path_to_backup_file.bak'
WITH
    MOVE 'logical_data_name' TO 'new_data_path.mdf',
    MOVE 'logical_log_name'  TO 'new_log_path.ldf',
    REPLACE,
    RECOVERY;

Restore the AdventureWorks database from a backup, moving files to new locations:

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH
    MOVE 'AdventureWorks_Data' TO 'D:\Data\AdventureWorks.mdf',
    MOVE 'AdventureWorks_Log'  TO 'L:\Logs\AdventureWorks.ldf',
    REPLACE,
    RECOVERY;

Option	Description
`MOVE`	Relocates the logical file names to new physical locations.
`REPLACE`	Allows overwriting an existing database.
`RECOVERY`	Brings the database online after restore.
`NORECOVERY`	Leaves the database non‑operational for additional restores (e.g., log chain).
`STANDBY = 'undo_file'`	Puts the database in read‑only standby mode with an undo file.

Full syntax reference:

RESTORE DATABASE
    { database_name | @database_name_variable }
    FROM
        { backup_device | @backup_device_variable }
        [ ,...n ]
    [ WITH
        [ FILE = file_number | @file_number_variable ]
        [ ,MEDIATYPE = { 'DISK' | 'TAPE' } ]
        [ ,MEDIANAME = 'media_name' ]
        [ ,NOUNLOAD ]
        [ ,REPLACE ]
        [ ,NORECOVERY ]
        [ ,STANDBY = 'undo_file_name' ]
        [ ,STOPAT = "date_time" ]
        [ ,STOPATMARK = mark_name ]
        [ ,STOPBEFOREMARK = mark_name ]
        [ ,CONTINUE_AFTER_ERROR ]
        [ ,RESTRICTED_USER ]
        [ ,RESTART ]
        [ ,RECOVERY ]
        [ ,MOVE = 'logical_name' TO 'physical_name' ]
        [ ,FORCE ]
        [ ,MAXTRANSFERSIZE = size ]
        [ ,BUFFERCOUNT = count ]
    ]

Best Practices

Always verify the backup integrity with RESTORE VERIFYONLY before restoring.
Use the MOVE clause to avoid overwriting critical files unintentionally.
Test restores on a non‑production server periodically.
Document the logical file names using RESTORE FILELISTONLY.

SQL Server Docs

Restore Database

When to Use

Basic Syntax

Best Practices

Related Topics