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.
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
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:
Step 2: Right click databases under object explorer and select “Restore Database…” to open the
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.
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:
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)
Step 6: On the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:
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.
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.
Step 8: A progress indicator appears as shown in the screenshot and a message box is displayed once the restore is successful.
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 that will not only fix your database with the least minimal data loss and potentially help you restore deleted records from the database.
Samuel Okudjeto is a technology enthusiast with great interest in database administration and analytics. He has many certifications including the Microsoft Certified Expert Professional. Along with 6+ years of hands-on experience, he holds a Masters of Science degree in Business Analytics. Read more
Have taken MySQL backup and now restoring that into DB through cmd. But it’s showing an error as the specific file can’t specified. Any help?
Hi Samuel,
We appreciate your depth research i.e. helpful for our ongoing project. We have limited time to try this software.