By: Rajendra Gupta | Updated: 2018-12-24 | Comments | Related: More > Professional Development Interview Questions DBA
Problem
In my previous tip, we learned about interview question related to SQL Server database backups. In this part of the interview series, we will learn about SQL Server database restore interview questions
Solution
In this tip, let's start with questions related to the database restores in SQL Server. Before going to the interview go through the below links as a starting point.
- SQL Server Backup Tips
- SQL Server Backup Tutorial
- SQL Server Restore Tips
- SQL Server Restore Tutorial
- SQL Server Recovery Models
Q1. What are the enhancements in SQL Server2017 for differential backups?
Answer: In SQL Server 2017, the dynamic management view sys.dm_db_file_space_usage contains a new column called modified_extent_page_count. This column gives information about how many extents are modified after the full backup. We can use this field to calculate percentage change occurred in the database by using the below query.
SELECT file_id,total_page_count, modified_extent_page_count, (100 * modified_extent_page_count)/total_page_count [percent_changed] FROM sys.dm_db_file_space_usage
Additional resources:
Q2. In SQL Server, normally we take transaction log backups for critical databases at a regular interval. Sometimes there might not be any change to the database but we issue a transaction log backup, as part of the regular schedule. Is there any other way to issue transaction log backups in SQL Server?
Answer: Prior to SQL Server 2017, we could only issue transaction log backups on a regular interval. SQL Server 2017 introduces a new DMF called sys.dm_db_log_stats. In this DMF, the column logsincelastbackup represents how much log data has been generated since the last transaction log backup. Therefore, we can use this feature in our script and configure transaction log backups based on the size of logs generated since the previous transaction log backup.
Additional resources:
Q3. Explain the below error.
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Answer: We cannot run an ALTER DATABASE, backup and database shrink command on a database in parallel.
Additional resources:
Q4. What is a SQL Server database restore?
Answer: A SQL Server database restore is the process of creating a point in time version of the database. We can restore the database backup to a separate server, instance and/or geographical location for these types of failures:
- Application failure
- Server failure
- Disk failure
- Database failure
- Database corruption
- Research data issue
- Data corruption
Additional resources:
Q5. What are the phases of a SQL Server database restore?
Answer: There are 3 phases of database restore:
- Data Copy: This phase involves copying the data, log, and index pages from the database to the backup file.
- Roll forward or Redo: The redo phase rolls forward that data to the recovery point. In this phase, a database typically has uncommitted transactions and is in an unusable state.
- Roll back or Undo: In this process, any uncommitted transactions are rolled back.
Additional resources:
Q6. What is the RESTORE FILELISTONLY command?
Answer: We can see a list of the database files that were backed up for the specific database. This command returns the logical as well as the physical name of the database and log files, type of file (data file, log file, a Full-Text Catalog or File Stream), filegroup name, file size, first and last LSN of the database backup, differential backup LSN, etc.
Additional Information:
Q7. Explain the SQL Server RESTORE HeaderOnly command.
Answer: With the SQL Server RESTORE HEADERONLY command we can see the backup header information for all backup sets on a particular backup device. It returns the backup name, description, backup type, backup compression, the username that performed the backup, database name, version, backup size, LSN details, flag to show copy-only backup, etc.
Additional resources
Q8. Suppose we have a large backup file placed on the server and we need to identify the source database version, the user who performed the backup from the backup file, etc. How can we do that?
Answer: We can use the SQL Server Restore HeaderOnly command to get this information. The syntax for the Restore HeaderOnly command is:
Restore header-only from disk='backup file path' Go
We can check the below columns to get the data in the output:
- User Name
- Server Name
- Database Name
- Database version
Additional resources:
Q9. How can we validate the SQL Server backup?
Answer: We can use the Restore VerifyOnly command to validate the database backup. It checks whether the backup is valid or not. It does not perform a database restore. If the backup is valid, the SQL Server Database Engine returns a success message.
RESTORE VERIFYONLY FROM DISK = ‘Backup File Path'; GO
Additional resources:
Q10. In my environment, we use both the native SQL Server backups as well as third-party backup tools. If I provide you a SQL Server backup file, could you identify which software was used for the backup?
Answer: We can check the backup tool used from the RESTORE LABELONLY command output from the backup media information returned.
RESTORE LABELONLY FROM DISK = N 'Database backup file location'
Additional resources:
Q11. What considerations do you need to take if you need to restore multiple database backups such as full backup followed by differential and transaction log backups?
Answer: We need to restore the full backup first with the NORECOVERY mode option. This mode leaves the database in a restoring state and does not perform a database recovery. We can restore subsequent databases backups with the NORECOVERY option until the last backup which requires the Recovery option to perform the database recovery.
Below is sample code with the NORECOVERY option:
RESTORE DATABASE DBName FROM DISK ‘Backup file path' WITH NORECOVERY
Additional resources:
Q12. We want to restore the production SQL Server database into a test environment. In a production environment, we have multiple drives separately for the data file and log files, but in the test environment, we do not have the same drive structure. Can we restore the database in that case or we need same drive structure as the source environment?
Answer: Yes, we can restore the database provided we have sufficient free disk space to accommodate the database files. We need to use the With Move clause to specify the new location for the data files as well as the log files individually.
Below is an example:
RESTORE DATABASE [DBName] FROM DISK = 'Backup File location' WITH MOVE N'FileName' TO 'File destination' --Do this for each log file and data file
Additional resources:
Q13. Explain the WITH REPLACE option in for the SQL Server Restore database command.
Answer: The SQL Server With Replace clause allows over writing an existing database when issuing a restore without first backing up the tail of the transaction log.
RESTORE DATABASE AdventureWorks FROM DISK = 'DB backup path' WITH REPLACE GO
Additional resources:
Q14. What are the steps to perform a SQL Server differential backup restore?
Answer: We need to restore full SQL Server database backup first then the most recent differential backup. Therefore, the steps will be:
- Restore full backup with NoRecovery clause
- Restore Differential backup with Recovery clause
Additional resources:
Q15. How can we identify when the last time my database was restored and where was it restored from?
Answer: We can verify the database restore history from the msdb database system tables.
Additional resources:
Q16. Suppose my SQL Server database is in full recovery mode and we are issuing full database backups followed by transaction log backups with a regular interval of 30 minutes. Due to some unforeseen circumstances, transaction log backups were not running for 3 to 4 hours. During this period, the transaction log size grew very large. One of the DBAs changed the database recovery model from full to simple in order to release the disk space and shrink the transaction log size. Is it recommended to switch to simple recovery model?
Answer: No, it is not recommended to switch from full to simple recovery model. Switching to simple recovery model breaks the transaction log backup chain, requiring a full backup before any new transaction log backups can be performed.
Switching from full to simple recovery model should only be performed under emergency scenarios or if you do not want the ability for point in time recovery.
Additional resources:
Q17. In the above scenario, if the application team informs the DBAs about bulk insert operations, can the DBAs switch to a bulk-recovery model and/or revert back to the full recovery model once the activity is completed?
Answer: Yes, we can switch from full to bulk insert recovery model to avoid transaction log growth due to bulk operations. We can switch between the full and bulk-logged recovery model without any issues in the log backup chain. However, if the databases are participating in Database Mirroring or an Availability Group, we cannot change the recovery model to bulk-logged since these technologies require the database to be in the full recovery model.
Additional resources:
Q18. What is SQL Server point in time recovery?
Answer: We can restore a SQL Server database at any point of time with the full and bulk-logged recovery model with regular transaction log backups. The RESTORE ... WITH STOPAT option allows restoring your database to a specific point in time.
Additional resources:
Q19. Suppose someone started a SQL Server database restore for a large database on my instance. How can you as an administrator view the restore completion status?
Answer: We can use the dynamic management view (sys.dm_exec_requests) to check to restore percentage. It contains the column percent_complete to track the progress of the database restore.
Below is a sample query to access the database restore status:
select percent_complete, * from sys.dm_exec_requests where a command like '%Restore%'
Q20. What are some common reasons for SQL Server database restore failures?
Answer: The most commons reasons for SQL Server database restore failures are:
- Permissions issues
- Insufficient free space on the drive
- Destination database is in use
- Syntax error
- Version difference between the source and destination servers
- Backup set permission issue
Q21: What are orphaned SQL Server users?
Answer: When we restore a SQL Server database from one server to another server the login id's stored in the source master database do not align with the login id's stored in the restored destination database. The logins and users are mismatched and the login cannot connect to the database. These users are called orphan users.
Additional resources:
Q22. Explain a SQL Server piecemeal Restore.
Answer: In the case of a multiple file group SQL Server database, we can restore the database at multiple stages. For example, we can restore the primary file group backup first and bring it online while the rest of the database can be restored at a later point in time. The piecemeal restore process works with all recovery models.
Additional resources:
Q23. Is it possible to restore a backup onto a different version of SQL Server?
Answer: Yes, we can restore a SQL Server database to a higher version of SQL Server than the source database version. For example, we can restore SQL Server 2008 backup to SQL Server 2012, but not vice-versa.
Q24. Explain the term RTO for a SQL Server database environment.
Answer: Recovery Time Objective (RTO) is the acceptable amount of time defined by the business when a database can be restored after an issue.
Next Steps
- Explore SQL Server Restore Options and Commands Tutorial
- Refer Microsoft Docs to learn more about SQL Server DB restore.
- Stay tuned for further tips on interview questions.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-12-24
