Summary: Let’s learn how to restore a SQL Server database backup for SQL Server 2012. There are various methods of taking database backups, which include using T-SQL code, SQL Server Management Studio, or third-party applications. These backups taken are purposely saved to be restored when the database becomes corrupt or crashes, migrating the database, making a copy of the database, and other business requirements. In this crash course, we will be focusing on how to restore SQL Server database backup by using the SQL Server Management Studio (SSMS) GUI, T-SQL code and SQL recovery software.
Prerequisites
For this backup restoration procedure, we assume that the database backup is readily available and the file location is known. We also have permissions to access the file/directory as well as there are no corruption or disk issues with the backup file. Also, the version of the database is not greater the version of the SQL Server on which the backup needs to be restored. For example; you cannot restore a database with version 130 (SQL Server 2016) on a SQL Server 2012 or version (110) – Read More
Steps to restore database backup by using SQL Server Management Studio in SQL Server 2012
Step 1: Start your SQL Server Management Studio and connect to the SQL instance you will be restoring the backup:
Connect to SQL Server Instance
Step 2: Right click databases under object explorer and select “Restore Database…” to open the dialog box.
Restore Database
Step 3: Verify under the source option if the correct source database is selected. You can click the dropdown list to select the specific database. You also need to specify the destination database for this restore.
NOTE: Restore to a new database by typing/specifying the new name of the destination database or select from the list to overwrite. You need to understand the implications of overwriting the existing database with the backup.
Select Specific Database
Step 4: On the restore database dialog box, there are two options for choosing the backup database. One is for the last backup taken and the second for selecting a specific data and time. Choose the one appropriate for you, then click on the “OK” Button:
Options for Choosing Database Backup
Two Options for Database Backup
Step 5: Now click on Files Option on the left pane. Restored files can be reallocated to the desired specified folders. You can also rename the physical file name of database files (MDF, NDF, and LDF)
Relocated the Files to Folder
Step 6: On the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:
- Overwrite the existing database (WITH REPLACE)
- Preserve the replication settings (WITH KEEP_REPLICATION)
- Prompt before restoring each backup
- Restrict access to the restored database (WITH RESTRICTED_USER)
Select an option for the Recovery state box. This box determines the state of the database after the restore operation.
RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.
RESTORE WITH NORECOVERY which leaves the database non-operational and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.
RESTORE WITH STANDBY which leaves the database in read-only mode. It undoes uncommitted transactions but saves the undo actions in a standby file so that recovery effects can be reverted.
NOTE: Restore operations will fail if there are active connections to the database. Check the Close existing connections option to ensure that all active connections between Management Studio and the database are closed.
Select Prompt before restoring each backup if you wish to be prompted between each restore operation. This is not usually necessary unless the SQL database is large, and you wish to monitor the status of the restore operation.
Database Restore Options
Step 7: Now click on the ok button to restore the database or if you wish to script your configurations into a TSQL code, then click “Script” and select appropriate action as shown below.
Script Option
Check Script of the Database
Step 8: A progress indicator appears as shown in the screenshot and a message box is displayed once the restore is successful.
Database Restore Progress Indicator
Success Message
TSQL Code Reference
--TSQL COMMAND TO RESTORE DATABASE DBXXX FROM A BACKUP FILE
USE [master]
RESTORE DATABASE [DBXXX]
FROM DISK = N'\MyServer\MyNAS\Backups\SQL_Backups\USERDB\FULL\DBXXX\
DBXXX_backup_2018_09_30_010002_2112459.bak'
WITH FILE = 1, NOUNLOAD, STATS = 5
GO
--CHECK THE STATUS OF THE RESTORE
SELECT PERCENT_COMPLETE, ESTIMATED_COMPLETION_TIME, *
FROM SYS.DM_EXEC_REQUESTS
WHERE COMMAND LIKE '%RESTOR%'
Conclusion
This demonstration is a perfect scenario where you are not facing any corruption, database inaccessibility, lost database files, and virus-infected databases. In this case, you can easily restore a database in a few clicks or write a simple T-SQL script as shown above to achieve a full database restore. Are you caught up in the worst scenario where you cannot complete a restore due to the possible factors mentioned earlier?
I would like to introduce to you a robust SQL Database recovery tool, Stellar Repair for MS SQL that will not only fix your database with the least minimal data loss and potentially help you restore deleted records from the database.
