How to Restore Corrupt Database in SQL Server 2016
Table of Contents
Introduction
SQL Administrators are well-versed with the Business Continuity Process and Disaster Recovery plan (BCP-DR) when the database gets corrupt and inaccessible. In general, the BCP-DR plan states that when a SQL database goes down, DBAs are required to check the corruption level in the SQL database with the help of DBCC_CHECKDB command, and resolve the issue with Microsoft-suggested SQL repair utilities. The next step is to restore the database with latest and healthy backup.
To restore the SQL database from backup, it is essential to analyze the type of SQL database backup and then choose the recovery process. DBAs using single backup can recover database by using the ‘RECOVERY’ feature. For different types of backups such as differential and transactional backups, it is better to use the ‘NORECOVERY’ option.
The issue arises when the backup is either not recent, or it is not available in a healthy state. In both cases, it is not possible to restore the database from backup, which makes repairing the database with a SQL repair software as the only solution.
Note: Before beginning to restore the corrupt database in SQL Server 2016, make sure the database is not being accessed by the users. The Database Admin must be the only person using the database to be restored.
The prerequisites
- Backup the active transaction log before restoring the database in bulk-logged or full recovery model
- DBA must have access to a certificate or asymmetric key to encrypt the database if the database available for restoration is encrypted.
The Requirements
- SQL server latest version is installed
- SQL Server Management Studio installed on the machine
- Stellar Repair for MS SQL is installed
Restore with RECOVERY and with NORECOVERY
To restore with RECOVERY option:
- Open SQL Server Management Studio and right click on Database.
- From the available options, select Restore Database option
- Select the database to restore. Check that the backup is available for that database and that it can be restored
- Select the option of ‘RESTORE WITH RECOVERY’
Alternatively, DBAs can use T-SQL command instead of SSMS interface to restore from the database:
RESTORE DATABASE [xyz] FROM DISK = N’c:sqlxyz.bak’ WITH FILE = 1, NOUNLOAD, STATS = 5, RECOVERY GO
To restore with NORECOVERY option
Restoring with NORECOVERY involves two processes, for example in case the database consists of a Full backup and a Transaction-backup then,
- The full backup is restored using the NORECOVERY T-SQL command
RESTORE DATABASE [xyz] FROM DISK = N’c:sqlxyz.bak’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5
- And transaction backup is restored using the T-SQL command for RECOVERY
RESTORE LOG [xyz] FROM DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackupxyz_LogBackup_2018-12-21_12-24-25.bak’ WITH FILE = 1, NOUNLOAD, STATS = 5, RECOVERY
Repair and restore with SQL Database repair software
An inaccessible SQL database may be due to corruption or damage in the database. Also, in extreme cases, if the backup is missing then you have the option of using the SQL database recovery software to repair the corrupt or damaged SQL Database:
- Download and install the software
- Locate the primary database file or MDF file
- Scan and verify the database components
- Save as new database at a new location or the same location as the recovered database
Note: Once the database is repaired, it is essential to automate the backup task of the recovered database with the help of the following command:
RESTORE DATABASE [xyz] FROM DISK = N’c:sqlxyz.bak’ WITH FILE = 1, NOUNLOAD, STATS = 5, RECOVERY GO
The backup command helps in maintaining a healthy backup for future reference.
Stellar Repair for MS SQL also helps in restoring the SQL database with a different name and even at a new location. Ensure that there is enough storage space to restore the database at that new location. Also, check for any potential conflicts with the existing files.
Conclusion
The role of a DBA spans a wide range of activities that are performed on routine basis. Regular maintenance of the server, database and SQL application, regular testing of database backup, checking security access restrictions, monitoring database activity levels are some of these tasks which help the DBAs to keep the SQL database issues away. But, SQL database issues can still happen to disrupt database accessibility. Restoring from healthy and latest backup or repairing the SQL database with a comprehensive database repair software for MS SQL ensures timely recovery of SQL database.