SQL Server Documentation

Restoring Your Database

This document provides a comprehensive guide to restoring your SQL Server databases. Proper backup and restore procedures are critical for data protection and disaster recovery.

Understanding Restore Scenarios

Restoring a database can be performed in several scenarios:

• Restoring a full database backup: The most common type of restore, bringing the database back to a specific point in time.
• Restoring file and filegroup backups: Used for partial restores or when dealing with very large databases.
• Restoring transaction log backups: Essential for recovering to a point after the last full or differential backup.
• Restoring system databases: Includes master, model, msdb, and tempdb.

Methods for Restoring Databases

You can restore databases using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL) commands.

Using SQL Server Management Studio (SSMS)

SSMS offers a user-friendly interface for performing restore operations.

1. Connect to your SQL Server instance in SSMS.
2. Right-click on the Databases node and select Restore Database....
3. In the Restore Database dialog box:
   • Select the Source: Choose Device and browse to your backup file, or select URL for Azure Blob Storage.
   • Choose the Backup set to restore.
   • Under Destination, select the Database to restore to. You can restore to a new database or overwrite an existing one.
   • Navigate to the Files page to verify or change the physical file locations if restoring to a different server or configuration.
   • Review the Options page for settings like overwriting existing databases and recovery states.
4. Click OK to start the restore process.

Using Transact-SQL (T-SQL)

T-SQL provides granular control over restore operations.

Restoring a Full Database Backup

This is the basic command to restore a full backup. Ensure the database does not exist or use the WITH REPLACE option to overwrite it.

RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Full.bak'
WITH NORECOVERY; -- Or RECOVERY if this is the only backup
GO

Restoring a Transaction Log Backup

This command restores a transaction log backup after a full backup.

RESTORE LOG YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Log.trn'
WITH NORECOVERY;
GO

Restoring to a Specific Point in Time

Use the STOPAT clause to restore to a precise moment.

RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Full.bak'
WITH NORECOVERY,
     STOPAT = '2023-10-27 10:30:00';
GO

RESTORE LOG YourDatabaseName
FROM DISK = 'C:\Backups\YourDatabaseName_Log.trn'
WITH RECOVERY; -- Use RECOVERY when the last log backup is applied
GO

Recovery States

Understanding recovery states is crucial for a successful restore, especially when applying multiple backups.

• RECOVERY: The database is fully recovered and ready for use. This is typically used when restoring the last backup in a sequence.
• NORECOVERY: The database is left in a restoring state, allowing you to apply additional transaction log or differential backups.
• STANDBY: The database is left in a read-only state, but can be recovered. This allows read-only access while further restores are pending.

Common Restore Options

• WITH REPLACE: Overwrites an existing database with the same name. Use with caution.
• WITH MOVE 'Logical_Data_Name' TO 'Physical_Data_Path': Allows you to specify new locations for data and log files.
• WITH RECOVERY or WITH NORECOVERY: As described above, controls the final state of the database.

Troubleshooting Restore Issues

• Access Denied: Ensure the SQL Server service account has read permissions on the backup file location.
• Corrupt Backup: If the backup file is corrupt, you may need to restore from an earlier backup or re-create the backup.
• Incorrect Sequence: Ensure you are applying backups in the correct order (full, differential, logs).