Read time: 4 minutes
SQL Server backup is done using different methods like T-SQL Code, SQL Server Management Studio, or using a third-party tool. The purpose of taking a backup of the database is to combat and reduce the chances of embarrassment during corruption, damage, crashing, etc., of the database or in case you wish to migrate the database or to create a copy of the database or other requirements.
Things to be taken care of before SQL backup restoration
Before you start the process of restoring database backup, you need to take care of a few things which are given under:
- Keep database backup ready.
- Keep the file location handy.
- You should have permission to access the file/directory.
- There should be no corruption of the backup file.
- The disk containing the backup file should be healthy.
- The version of the database should not be higher than the version of the SQL Server.
Methods to restore database backup in SQL server 2012
These methods can be used if you had taken care to backup your SQL Server database.
Method 1: Using enterprise manager in SQL server 2012
Follow the steps given under to restore database backup in SQL Server 2012
- Start SQL Server 2012.
- Expand the database and select database right-click on it.
- Select Tasks > Restore > Database. The Restore dialog box will open.
- Select the database from the list under Source.
- Select the database from the list under Destination.
- Select the date of the database by clicking on Timeline. This will open a new dialog box.
- Select either the Last backup taken or Specific date and time. Press OK
- Select Files (on the left) and check to Relocate all files to folder.
- Select options (just below Files on the left).
- Select options viz. Restore options, Tail-Log backup, Server connections, Prompt as per need. Press OK.
- This will start Restoring files; once it is completed, a message will show that the database has been restored successfully.
Method 2: Using SQL server management studio
- Start SQL Server Management Studio (SSMS).
- Connect SSMS to that SQL Instance to which you will restore the backup.
- Under Object Explorer, right-click Databases.
- Select Restore Database. This will open a new dialog box.
- Under Source, select required Database (you can select it from the drop-down list)
- Under Destination, select Database to be restored.
- Under Restore, you have 2 options shown to restore the backup database, viz. Last backup taken or Select a specific date and time. Choose the desired option. Press OK
- Now Select ‘Files’ on the left pane. Check Relocate all files to folder to Restore database files by selecting the desired specified folders. You may rename the database files as well.
- Press Options on the left, below Files. Under Restore Options on the right, select any options as per your need.
- Select the desired option for Recovery State viz.:
- RESTORE WITH RECOVERY
This default behavior rolls back the uncommitted transactions, and thus the database can be used readily. It does not restore additional transaction logs. You should select this option if you want to restore all the necessary backups instantly. - RESTORE WITH NONRECOVERY
This does not roll back the uncommitted transactions, and the database is non-operational. It restores additional transaction logs. The database can only be used after recovery. - RESTORE WITH STANDBY
It renders the database in read-only mode, and uncommitted transactions are undone. To enable recovery effects, it saves the undo actions in a standby file.
NOTE: Do remember to check Close existing connections to close all active connections between SQL Server Management Studio and the database, or else it will render Restore Operations fail.
- RESTORE WITH RECOVERY
- Click OK if you wish to restore the database. Once the restore is successful, a message box will display it.
Method 3: Using new version of SQL server management studio 19
The modern version of SQL Server Management Studio (19.0.1) has a different user interface than the previous one, so you must also know how to restore the full Database here. The new SSMU will also support the latest versions of SQL Server (2022, 2019, 2016). Follow the procedure –
- In Object Explorer, connect with the correct SQL Server Database Engine and expand it to connect with the list of Databases.
- Right-click and choose ‘Restore Database.’
- The General wizard opens, and in the Source section, multiple options exist to select the Backup file for restoration. There are the following options –
- Leave the Last Backup taken as the default value in the Restore to the box. Alternatively, choose Timeline to open the Backup Timeline dialogue box and manually select a point in time to halt the recovery activity.
- Choose the backups you want to restore from the Backup sets to restore grid. The backups that are accessible for the given location are shown in this grid. A recovery strategy is automatically present. You can alter the choices in the grid to overrule the proposed recovery path. When you deselect a previous backup, backups that depend on its restoration are automatically deselected as well.
- If applicable to your scenario, you can choose any of the following choices on the Options page of the Restore options panel to examine or pick the advanced options:
6.1 WITH options- Overwrite the existing database (WITH REPLACE)
- Preserve the replication settings (WITH KEEP_REPLICATION)
- Restrict access to the restored database (WITH RESTRICTED_USER)
6.2 In the Recovery state box, elect an option to manage the state of database after the restoration.
- RESTORE WITH RECOVERY.
- RESTORE WITH NORECOVERY.
- RESTORE WITH STANDBY.
- Manage other options and click OK to start the restoration.
Database
From the drop-down list, pick the Database you want to restore. Only databases whose Backup was taken under the ‘msdb’ backup history are in the list.
Device
There is a browser (…) button on which you can click to open the dialog box to choose the Backup file.
In the dialog box, choose the backup media type first, then Add the Backup files from various sources.
When the Database is selected in the Restore Database section, you can change the name of the database file or restore it with its original name.
Conclusion
We hope that you found this write-up helpful in restoring database backup in SQL Server 2012. Above mentioned methods will not work in case the backup is inaccessible, corrupt, damaged, or lost. In such scenarios, you need a powerful and efficient third-party tool like Kernel SQL Backup Recovery, which can recover data from corrupted SQL backup files; it supports the recovery from all SQL Server versions, maintaining the integrity of data. In case you encounter any scenario of corruption of SQL backup, then also you need the help of the SQL Backup Recovery tool
