Sql Server Restoring Databases Complete Guide

Top 10 Questions and Answers on SQL Server Restoring Databases

1. What are the different types of database restores available in SQL Server?

• Full Database Restores: Restore an entire database.
• Point-in-Time Restores: Restore the database to a specific point in time using transaction log backups.
• Differential Restores: Restore a full backup followed by the most recent differential backup.
• File and Filegroup Restores: Restore individual files or filegroups within a database.
• Tail-Log Backups: Important for point-in-time restores, capturing transactions after the last log backup.
• Partial Restores: Restore a primary filegroup and any secondary filegroups, leaving the rest of the database unavailable.

2. How do you restore a database from a full backup?

Answer: To restore a database from a full backup, you can use the RESTORE DATABASE command. Here’s a basic example:

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH FILE = 1,
MOVE 'MyDatabase_Data' TO 'C:\Data\MyDatabase_Data.mdf',
MOVE 'MyDatabase_Log' TO 'C:\Logs\MyDatabase_Log.ldf',
REPLACE;

• MOVE is used to specify the destination of the data and log files.
• REPLACE allows the restore operation to overwrite an existing database with the same name.

3. What is a differential backup, and when should you use it?

Answer: A differential backup captures only the data changed since the last full backup. You should use it to reduce the time required for restores after a full backup. Differential backups are useful in scenarios where full backups are taken weekly, and daily restores are needed.

4. How do you perform a point-in-time restore in SQL Server?

Answer: To perform a point-in-time restore, you need a full backup and a series of transaction log backups up to the point in time you want to recover to. Here is an example of the steps involved:

• Restore the full backup with NORECOVERY:

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH NORECOVERY;

• Restore differential backup (if used) with NORECOVERY:

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Diff.bak'
WITH NORECOVERY;

• Restore log backups with NORECOVERY up to the point just before the desired point:

RESTORE LOG MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Log1.trn'
WITH NORECOVERY;

• Finally, restore the last log backup with the STOPAT clause:

RESTORE LOG MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Log2.trn'
WITH STOPAT = '2023-10-01 12:00:00', RECOVERY;

5. What is the difference between WITH RECOVERY and WITH NORECOVERY options in restoring backup files?

Answer:

• WITH RECOVERY (default): Completes the restore sequence, making the database accessible. It rolls forward the database to the end of the last backup. Use this option when you have restored all necessary backups.
• WITH NORECOVERY: Leaves the database in a restoring state, suitable for sequential restores (like differential or transaction log backups). Do not use RECOVERY on the last log backup of a point-in-time restore.

6. How do you restore a database to a new name or location?

Answer: When restoring a database to a new name or location, use the MOVE clause to specify the new paths for the data and log files and replace the database using the REPLACE option.

RESTORE DATABASE MyNewDatabase
FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH FILE = 1,
MOVE 'MyDatabase_Data' TO 'D:\Data\MyNewDatabase_Data.mdf',
MOVE 'MyDatabase_Log' TO 'D:\Logs\MyNewDatabase_Log.ldf',
REPLACE;

7. What are the steps for restoring a detached database?

Answer: A detached database does not have a backup file. To attach a detached database, use the CREATE DATABASE ... FOR ATTACH statement.

CREATE DATABASE MyDatabase
ON (FILENAME = 'C:\Data\MyDatabase_Data.mdf'),
   (FILENAME = 'C:\Logs\MyDatabase_Log.ldf')
FOR ATTACH;

Ensure that the SQL Server has sufficient permissions to access the specified files.

8. How can you verify that a restore was successful in SQL Server?

Answer: After restoring a database in SQL Server, verify its integrity and contents using the following methods:

• Check the RESTOREHISTORY view in the msdb database to confirm the restore operation:

SELECT * FROM msdb.dbo.RESTOREHISTORY WHERE destination_database_name = 'MyDatabase';

• Verify the database status and recently restored date in the sys.databases view:

SELECT name, state_desc, create_date, modify_date
FROM sys.databases
WHERE name = 'MyDatabase';

• Run the DBCC CHECKDB command to check the logical and physical integrity of the data and pointers:

DBCC CHECKDB ('MyDatabase') WITH ALL_ERRORMSGS, NO_INFOMSGS;

9. What is a partial restore in SQL Server, and how do you perform one?

Answer: A partial restore recharges only the primary filegroup and one or more secondary filegroups. This is useful when non-primary filegroups are not critical and can be restored later. Steps for a partial restore:

• Restore the primary filegroup and mark the restore as NORECOVERY:

  RESTORE DATABASE MyDatabase
  FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
  WITH PARTIAL, RECOVERY;
  

• Add read_only filegroups that need immediate access:

  RESTORE DATABASE MyDatabase
  FILEGROUP = 'MySecondaryFG1'
  FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
  WITH RECOVERY;
  

• Restore remaining filegroups as necessary.

10. How can you handle a restore failure in SQL Server?

Answer: If a restore operation fails, consider the following troubleshooting steps:

• Check the error messages: They’ll provide specific reasons for the failure (file permissions, corruption, incorrect file paths).
• Verify file permissions: Ensure SQL Server has access to the backup files and the paths where the database files will reside.
• Check the backup integrity: Use RESTORE VERIFYONLY FROM DISK to check if the backup files contain valid data.
• Consider Placeholders: If the restore fails due to unrecovered dropped filegroups, create placeholders.
• Redo the backup: Ensure the files are not corrupted and the backup process succeeded.
• Examine the DBCC CHECKDB results: If the problem might be related to data corruption, run DBCC CHECKDB on the source database before attempting the restore.