So far, we’ve discussed a lot about database backup commands. In this article, we’ll discuss more on database restore or database recovery processes. When you initiate a restore process, it undergoes a series of internal stages to restore or recover the data to the specific point of time.
Introduction
In this article, you’ll see the FAQs and answers about the database restore and recovery internals. To learn a lot about SQL Server database backup-and-restore (recovery) internals, you can refer the full list of topics at the bottom.
In this article, we’ll answer the following topics.
- Define database restore
- Define the database recovery process
- Check the state of the backup file
- Check the number of file(s) in a backup file
- Identify the database version from a backup file
- Check the backup software tools used for backup
- Perform simple and multiple ways to restore a database
- Explain WITH OVERWRITE option
- Explain WITH NORECOVERY option
- Restore differential or t-log backup file
- Understand Other restore types STANDBY/READONLY
- UExplain WITH REPLACE option
- Explain WITH MOVE option
- Restore the database using a Split files
- Detail Piecemeal restore process
- Explain Point-in-time recovery
- Describe the Page-Level-Restore process
- Explain Recovery-Only database restore
- Explain WITH STOPAT option
- Generate restore data commands using dynamic T-SQL
Questions
In this section, let us deep-dive into the concepts of the database restore options.
1. What is a database restore?
It is the process of reconstructing the data to a usable state from database backup files in order to facilitate database operations.
2. What do you mean by database recovery?
Database recovery is the process of reconstructing the data that has been lost or it may be due to human errors (accidentally deletion) or hardware corruption or catastrophic failure made the data inaccessible. The data recovery typically refers to the restoration of data to a point where there is no or minimal data loss.
3. How do you check the backup file is a usable state?
The RESTORE VERIFYONLY command is used to check or validate the backup and it will ensure that the backup file is readable form.
|
1 |
RESTORE VERIFYONLY FROM DISK = N'F:\PowerSQL\PowerSQL.BAK' |
4. How do you find the how many files are there .bak\trn files?
The RESTORE FILELISTONLY command is used to list all the files related to the specified backup file.
|
1 |
RESTORE FILELISTONLY FROM DISK = N'F:\PowerSQL\PowerSQL.BAK' |
5. How do you find the database version of SQL Server by using the backup file?
The RESTORE HEADERONLY command output the header information of the backup.
|
1 |
RESTORE HEADERONLY FROM DISK = N'F:\PowerSQL\PowerSQL.BAK' |
6. How do you find software that was used to create the database backup?
The RESTORE LABELONLY command output the backup media information. In the output, we can see a software column.
|
1 |
RESTORE LABELONLY FROM DISK = N'F:\PowerSQL\PowerSQL.BAK' |
7. How do you do a simple database restore in SQL Server?
To restore a simple database from a backup file, run the following command
|
1 |
RESTORE DATABASE PowerSQL FROM DISK =N'F:\PowerSQL\PowerSQL_FULL_20171012_1.BAK' |
The different ways to perform database restore operations:
- Using T-SQL, the database restore commands are almost similar to backup database commands. To perform database restore, you just need to change the word “backup” to “restore” and “to” to “from” followed by restore and recovery options
-
Using SQL Server Management Studio, run through the following steps:
- Browse Object Explorer
- Right-click Databases
- Click Restore Database
- Select Source in the restore section
- Select From Device, and choose the browse
- Click Add to select the backup file
- Select the Backup set to restore
- Click the options tab, Enable the checkbox to “overwrite the existing database”
- Select the recovery state (RECOVERY or NORECOVERY)
- Click Ok
-
Using PowerShell, this is one of the ways to restore a database in PowerShell
- Open the PowerShell ISE
- Load the SQLServer Module
-
Define the variables
- Database name
- Backup file location
- Data file location
- Log file location
- Prepare the restore command
- Invoke the SQL string using Invoke-SQLCmd
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Import-Module sqlServer $dbname = "SQLShackDemo" $backupFile = "\\aqdbt01\f$\PowerSQL\SQLShackDemo_07132018.bak" $dataFile = "\\aqdbt01\f$\PowerSQL\SQLShackNewDB.mdf" $logFile = "\\aqdbt01\f$\PowerSQL\SQLShackNewDB_log.ldf" $backupSql = @" USE [master] IF EXISTS (SELECT * FROM sys.databases WHERE name = '$dbname') ALTER DATABASE [$dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [$dbname] FROM DISK = N'$backupFile' WITH FILE = 1, MOVE N'SQLShackDemo' TO N'$dataFile', MOVE N'SQLShackDemo_log' TO N'$logFile', NOUNLOAD, REPLACE, STATS = 5 ALTER DATABASE [$dbname] SET MULTI_USER "@ Invoke-Sqlcmd -ServerInstance hqdbt01 -Query $backupSql |
8. How do you restore a database from full backup with overwrite option?
The following command is used to restore the database using the specified backup file. If the database already exists, it will overwrite the database files. If the database doesn’t exist, it will create the database and restore the files to the specified location in the backup command.
|
1 2 |
RESTORE DATABASE SQLShack FROM DISK N'F:\PowerSQL\SQLSHACK_FULL_20171012_1.BAK' WITH OVERWRITE |
9. How does a full backup restore allow additional restores such as a differential or transaction log backup?
Using NORECOVERY option, this option leaves the database in a restoring state after the full database restore has completed. This will allow you to restore additional database backup files in order to get the database more current
|
1 2 |
RESTORE DATABASE SQLShack FROM DISK N'F:\PowerSQL\SQLSHACK_FULL_20171012_1.BAK' WITH NORECOVERY |
10. How do you do a differential backup file restore?
To restore a differential backup, the options are exactly the same.
- You need do a full database restore with the NORECOVERY option
- Initiate differential restore using the following command
- Bring the database online, if you’ve no other file to restore.
|
1 2 3 4 |
RESTORE DATABASE SQLShack FROM DISK =N'F:\PowerSQL\SQLSHACK_DIFF_20171012_1.BAK' WITH NORECOVERY GO RESTORE DATABASE SQLShack WITH RECOVERY GO |
11. What are other types of database restore that you can perform in SQL Server?
You can think of Standby / Read-Only Restore.
This mode is also known as STANDBY mode and can be used for reading operations.
|
1 2 |
RESTORE LOG PowerSQL FROM DISK='F:\PowerSQL\PowerSQL_log.trn' WITH STANDBY=N'F:\PowerSQL\PowerSQL_STANDBY_20171012_1.BAK' |
The standby option is for High-availability setup such as Log Shipping. In this setup, you have configured a warm standby server for disaster recovery. SQL Server engine designed to offer the ability to have the secondary database in a restoring state or in a standby state which allows read-only activity on the secondary database.
12. How does Restore with move option different from normal restore operation?
The RESTORE … WITH MOVE option allows you to specify a new file location for the newly created database. Also, if you are restoring a database from another instance with different file locations, then you need to use this move option.
|
1 2 |
RESTORE DATABASE [SQLShackDemoTest] FROM DISK = 'F:\PowerSQL\SQLShackDemo_FULL.BAK' WITH MOVE N'SQLShackDemo' TO 'F: \PowerSQLTest\ SQLShackDemoTest.mdf', MOVE N' N'SQLShackDemo' _log' TO 'F: \PowerSQLTest\ SQLShackDemo_log.ldf' |
13. How do you do a restore full database from multiple backup files?
SQL Server supports an option of write the backup data to multiple files. In some cases, the database backup file splitting is to manage the workloads of the system. In this case, it is assumed to be full backup file is available and split into multiple files. The process is similar and we will perform database restore using the following T-SQL:
|
1 2 3 4 5 6 7 |
RESTORE DATABASE PowerSQL FROM DISK =N'F:\PowerSQL\PowerSQL_FULL_20171012_1.BAK' ,DISK = N'F:\PowerSQL\PowerSQL_FULL_20171012_2.BAK' ,DISK = N'F:\PowerSQL\PowerSQL_FULL_20171012_3.BAK' WITH REPLACE , MOVE 'PowerSQL' TO 'F:\PowerSQL\PowerSQL_data.mdf', MOVE 'PowerSQL_Log' TO 'G:\PowerSQL\PowerSQL_data.mdf_log.ldf' |
14. What is Piecemeal restore and explain the internals?
Piecemeal restore is a process to help database restore that contains multiple filegroups (s) and it is recovered in multiple stages.
- Piecemeal restore process involves a series of the database restore, starting with the Primary filegroup followed by one or more secondary filegroup(s).
- Piecemeal restore process works with all recovery models
- The restore process maintains a sequence called partial-restore sequence
|
1 |
Restore database SQLShack FILEGROUP='Primary' from disk=N'F:\PowerSQL\PowerSQL_FULL_20171012.BAK' WITH NORECOVERY, PARTIAL |
See Also,
Database Filegroup(s) and Piecemeal restores in SQL Server
15. What is Point-in-time Recovery?
The RESTORE … WITH STOPAT option allows you to restore your database to a point in time. This gives you the ability to restore a database prior to an event that occurred that was detrimental to your database. In order for this option to work, the database needs to be either in the FULL or Bulk-Logged recovery model and you need to be doing transaction log backups.
16. Explain Point-in-time restore with STOPAT option
To perform the point-in-time database restores action, the database recovery model to be in either the Full or Bulk-Logged.
- First, identify the valid FULL or Bulk-logged backup file
- Restore the database with NORECOVERY
- Restore the log
This will restore the database to a point “July 16, 2018, 01:38:00 PM”.
|
1 2 3 4 5 6 7 |
RESTORE DATABASE SQLShack FROM DISK = N'F:\PowerSQL\PowerSQL_FULL_20171012.BAK' WITH NORECOVERY GO RESTORE LOG SQLShack FROM DISK = N'F:\PowerSQL\PowerSQL_LOG_20171012.TRN' WITH RECOVERY, STOPAT = 'July 16, 2018 01:38:00 PM' GO |
17. What is Page-level restore in SQL Server?
Page-level restore is a process or technique can be used to replace corrupted pages in a database with an uncorrupted data from the database backup file. If you have a corrupt page(s) in SQL Server database, instead of restoring a complete database, you can restore only those pages that are corrupted from the available valid database backup file set. The process can be performed via SSMS or T-SQL. You can query msdb.suspect_pages’ to identify corrupted pages and track the pages that are marked as “suspect” in the table as well.
|
1 |
Restore database SQLShack PAGE ='1:153,1:202' from disk=N'F:\PowerSQL\PowerSQL_20171012.BAK' WITH RECOVERY |
The entire process is very well explained in the following article
How to perform a page level restore in SQL Server
18. What is WITH REPLACE option in Restore command?
The “RESTORE database…WITH REPLACE” option allows overwriting an existing database while doing a database restore process.
The following restore database command overwrite the database files
|
1 |
RESTORE DATABASE [ApexSQLCrd] FROM DISK = N'F:\PowerSQL\ApexSQLCrd_20171012.BAK' WITH REPLACE |
19. How do you perform the Recovery-Only database restore?
The scope of recovery-only restore is to the database or file or filegroup(s) level.
Before performing database recovery, restore the entire database make sure it is consistent. However, it is also possible that the recovery can be applied without restoring an entire database. For example, read-only file.
|
1 |
RESTORE DATABASE SQLShack WITH RECOVERY |
For filegroup, issue the following command
|
1 |
RESTORE DATABASE SQLShack FILEGROUP='SQLShackFG' WITH RECOVERY |
20. How do you generate restore one/one or more/all database script dynamically?
- In the first T-SQL, the database names and backup file location are passed using a variable.
- In the following steps, you can customize T-SQL to directly feed all the database names by querying the sys.databases object or you can hard code the database names in the sub-query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
DECLARE @path varchar(50)='F:\PowerSQL', @dbname varchar(100)='SQLShackDemo' Select 'RESTORE DATABASE '+ '['+sd.Name+']'+ ' FROM DISK = N'+''''+@path+'\'+sd.Name+'_20171012.BAK'+''''+' WITH MOVE N'+''''+sfm.Name+''''+ ' TO N'+''''+sfm.Physical_Name+''''+','+' MOVE N'+''''+sfl.Name+''''+' TO N'+''''+sfl.physical_name+''''+','+' REPLACE, STATS = 5 , NORECOVERY' from sys.master_files sfm Join sysdatabases sd On sfm.database_id=sd.dbid and sfm.file_id=1 Join sys.master_files sfl on sfl.database_id=sd.dbid and sfl.file_id=2 and sd.Name in(@dbname) DECLARE @path varchar(50)='F:\PowerSQL' Select 'RESTORE DATABASE '+ '['+sd.Name+']'+ ' FROM DISK = N'+''''+@path+'\'+sd.Name+'_20171012.BAK'+''''+' WITH MOVE N'+''''+sfm.Name+''''+ ' TO N'+''''+sfm.Physical_Name+''''+','+' MOVE N'+''''+sfl.Name+''''+' TO N'+''''+sfl.physical_name+''''+','+' REPLACE, STATS = 5 , NORECOVERY' from sys.master_files sfm Join sysdatabases sd On sfm.database_id=sd.dbid and sfm.file_id=1 Join sys.master_files sfl on sfl.database_id=sd.dbid and sfl.file_id=2 and sd.Name in(select name from sys.databases where database_id>4) DECLARE @path varchar(50)='F:\PowerSQL' Select 'RESTORE DATABASE '+ '['+sd.Name+']'+ ' FROM DISK = N'+''''+@path+'\'+sd.Name+'_20171012.BAK'+''''+' WITH MOVE N'+''''+sfm.Name+''''+ ' TO N'+''''+sfm.Physical_Name+''''+','+' MOVE N'+''''+sfl.Name+''''+' TO N'+''''+sfl.physical_name+''''+','+' REPLACE, STATS = 5 , NORECOVERY' from sys.master_files sfm Join sysdatabases sd On sfm.database_id=sd.dbid and sfm.file_id=1 Join sys.master_files sfl on sfl.database_id=sd.dbid and sfl.file_id=2 and sd.Name in('SQLShackDemo','SQLShackInMemDB') |
That’s all for now…stay tuned for more updates.
Table of contents
References
- Restore a SQL Server Database to a Point in Time (Full Recovery Model)
- Recover a Database Without Restoring Data (Transact – SQL)
- Restore Statements (Transact – SQL)
- Piecemeal Restores (SQL Server)
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021