7. DATABASE RESTORE

Database restore in SQL Server is the process of bringing a database back to a consistent, usable state from backups. What is needed depends on the recovery model and the restore goal.

Key concepts

• Complete database restore: Restores the whole database. The database is offline for the entire restore. All parts must be recovered to the same point in time with no uncommitted transactions.
• Recovery models:
  • Full recovery model: Supports point-in-time recovery using log backups.
  • Simple recovery model: Does not support point-in-time recovery within a backup; only full (and optionally differential) backups are used.
• Restore sequence: One or more RESTORE operations that move data through the restore phases to the final recovered state.

Restoring under the full recovery model Under the full recovery model, the typical goal is to restore to the point of failure or to a specific recovery point.

To restore to the point of failure:

1. Back up the tail of the log
   • Take a tail-log backup of the active transaction log. This captures all log records since the last log backup.
   • If the active log is unavailable, transactions in that part of the log are lost.
2. Restore the most recent full database backup
   • Use RESTORE DATABASE with NORECOVERY so the database stays in the restoring state and can accept additional backups:

          RESTORE DATABASE database_name
            FROM backup_device
            WITH NORECOVERY;
     

3. Restore the most recent differential backup (if any)
   • Restoring a differential reduces the number of log backups needed:

          RESTORE DATABASE database_name
            FROM differential_backup_device
            WITH NORECOVERY;
     

4. Restore all subsequent log backups in sequence
   • Start with the first log backup taken after the last restored backup and apply each in order with NORECOVERY:

          RESTORE LOG database_name
            FROM log_backup_device
            WITH NORECOVERY;
     

5. Recover the database
   • After the last required log (including the tail-log backup) is restored, bring the database online:

          RESTORE DATABASE database_name WITH RECOVERY;
     

   • Alternatively, specify WITH RECOVERY on the final RESTORE LOG.

Point-in-time recovery

• Under the full recovery model, a database can be restored to a specific recovery point within a log backup:
  • By date and time
  • By marked transaction
  • By log sequence number (LSN)
• This is done by specifying the appropriate point-in-time options on the final log restore (details are in the RESTORE documentation referenced in the context).

Untrusted sources

• Avoid restoring or attaching databases from unknown or untrusted sources.
• If such a database must be used, first restore it on a non-production server and run DBCC CHECKDB, and review user-written code (stored procedures, etc.).

Restoring under the simple recovery model

• The goal is still to restore the whole database to a consistent point.
• Under the simple recovery model:
  • The database cannot be restored to a specific point in time within a backup.
  • Recovery is based on full (and optionally differential) backups only.

Basic restore with SSMS (GUI)

• Using SQL Server Management Studio:
  1. Connect to the SQL Server instance.
  2. Right-click Databases in Object Explorer and select Restore Database....
  3. Choose the source (Device or Database) and select the backup file (.bak) or backup history.
  4. Confirm the target database name.
  5. Click OK to start the restore.

Basic restore with Transact-SQL

• Example of restoring a database from a .bak file:

    USE [master];
    GO
    RESTORE DATABASE [SQLTestDB]
    FROM DISK = N'C:\\...\\SQLTestDB.bak'
    WITH FILE = 1, NOUNLOAD, STATS = 5;
    GO
  

Restore options in SSMS

• General page:
  • Database: Name of the database to restore (existing or new).
  • Restore to: Defaults to “To the last backup taken”. A Timeline option opens the Backup Timeline dialog to choose a specific date/time to restore to.
  • Restore Plan: SSMS uses backup history in msdb to suggest a restore plan (latest full, latest differential, then all required logs). Manual selection is possible; SSMS validates sequence correctness.
• Options page:
  • Overwrite the existing database (WITH REPLACE):
    • Allows overwriting an existing database with the same name, even from a different source database.
    • Use with caution.
  • Preserve the replication settings (WITH KEEP_REPLICATION):
    • Keeps replication settings when restoring a backup of a published database to another server.
    • Available only when restoring WITH RECOVERY.
  • Tail-log backup:
    • If the chosen point-in-time requires a tail-log backup, SSMS can enforce and configure it.
  • Server connections:
    • Close existing connections: Sets the database to single-user mode during restore and back to multi-user afterward to avoid failures due to active connections.
  • Prompt before restoring each backup:
    • Pauses between backup restores, useful when media changes are needed.
    • If the sequence is interrupted, the database remains in restoring state and can later be continued with the appropriate restore task (database, files/filegroups, or transaction log).

Restoring using Visual Studio Code (MSSQL extension)

• The MSSQL extension provides a guided restore dialog:
  • Start: In Connections view, right-click a database → Restore Database.
  • Restore from Database: Uses backup history on the same instance.
    • Select Source Database, Target Database, and backup sets to restore.
  • Restore from Backup File: Uses a .bak file accessible to SQL Server.
    • Select backup file, specify Target Database, choose backup sets.
  • Restore from URL: Uses a backup in Azure Blob Storage.
    • Sign in to Azure, select tenant, subscription, storage account, container, and blob, then specify Target Database.
  • Actions: Restore executes, Script generates equivalent T-SQL, Cancel closes the dialog.

Security and permissions (from Q&A context)

• To restore a database over an existing one:
  • Required: membership in sysadmin or dbcreator fixed server roles, or being the owner of the database.
• To change database compatibility level after restore:
  • Required: membership in the db_owner database role.
• Best practice: explicitly set the database owner after restore to avoid mismatches between database metadata and sys.databases.

Corruption and restore

• If DBCC CHECKDB shows corruption and restore is needed:
  • Restore from the most recent clean backup plus subsequent log backups.
  • If corruption reappears, the root cause is typically outside SQL Server (I/O subsystem, hardware, etc.). Remaining on faulty hardware is not viable; investigate or move to new hardware.

High-level restore workflow summary

1. Determine recovery model (full or simple) and restore goal (full, point-in-time, point-of-failure).
2. Collect required backups:
   • Full backup (mandatory).
   • Latest differential backup (optional, if used).
   • All required log backups (full recovery model).
   • Tail-log backup if restoring to point-of-failure.
3. Plan restore sequence:
   • Full → differential → logs → tail-log.
4. Execute restore:
   • Use T-SQL RESTORE or tools (SSMS, VS Code MSSQL extension).
   • Use NORECOVERY until the final step, then WITH RECOVERY.
5. Post-restore steps:
   • Verify database (e.g., DBCC CHECKDB on non-production first for untrusted sources).
   • Set database owner and compatibility level as needed.
   • Reconfigure replication or other features if applicable.

References:

• Complete Database Restores (Full Recovery Model)
• Complete Database Restores (Simple Recovery Model)
• Restore database (General page)
• Restore Database (Options Page)
• Quickstart: Backup and restore a SQL Server database with SSMS
• Database operations
• SQL Server 2022 : Issue Restoring Database from 2014 Fails Without 'sysadmin' Role - Microsoft Q&A
• SQL Server database corruption issue - Microsoft Q&A
• How to recover deleted sql server - Microsoft Q&A
• DB recovery in a semi-annual SQL plan - Microsoft Q&A
• Faster way to restore a MSSQL DB hosted on storage spaces. - Microsoft Q&A