How to restore an SQL server database after an unexpected crash?
You can restore a SQL Server database after an unexpected crash from the most up-to-date backup. If the backup is obsolete or not available, then use the SQL server’s built-in repair utility -DBCC CHECKDB command. The best choice in this situation is to use a professional MS SQL repair tool to restore database without any data loss. In this article, we will discuss the methods to restore SQL database after an unexpected server crash or failure.
Unexpected SQL server crash – reasons and consequences
The SQL Server may unexpectedly crash or stop responding due to various reasons, like hardware failure, bugs in the server code, sudden power failure, etc. After an unexpected server crash, facing inconsistency issues in the database or corruption of tables are quite common. When the tables or indexes are corrupted, you may fail to open or access the tables or encounter errors when trying to open them.
How to restore MS SQL database after a sudden server crash
SQL Server or database crashes often lead to corruption that makes the database unreadable or changes its state to SUSPECT mode. To recover the database after such an event, follow the methods mentioned below.
Method 1 – Restore the database from backup
Like other servers, SQL Servers allow you to create a backup of your database. You can restore your database from the backup in case of corruption or any other event. Regardless of the type of backup methods you’ve used to create a backup file, you can easily restore it. But your backup (file) should be readable, complete, and free from corruption. You can use the RESTORE VERIFYONLY statement to check the backup file. The command’s success message clearly means that your backup file is free from corruption. You can use it to restore the corrupt database file.
To restore SQL database .bak file, you can use the GUI or T-SQL statements. Before using any of the methods, first make sure:
- The backup file is compatible with the SQL Server version you’re using to restore.
- You have RESTORE permissions.
- You have closed all active connections to prevent the restore process from failing.
Now, restore the backup file by simply executing the query as given below:
RESTORE DATABASE admin54
FROM DISK = ‘Z:\SQLServerBackups\admin54.bak’ ;
RESTORE DATABASE stellardb54
Method 2 – Use DBCC CHECKDB command to repair SQL database
If the SQL backup file is corrupted or unreadable, then you can use the DBCC CHECKDB repair options to repair the corrupt SQL database. If you’re not able to access the database or your database is marked in RECOVERY PENDING or SUSPECT state, then you can try setting it in EMERGENCY mode. This mode helps you read and access the database and run the repair command on it, like DBCC CHECKDB. To set the database in EMERGENCY mode, use this command:
ALTER DATABASE [admin54] SET EMERGENCY
Once you’re able to access the database, then follow the steps given below to use repair options in DBCC CHECKDB command:
Set the database to SINGLE USER mode by using the following command:
ALTER DATABASE Dbtesting SET SINGLE_USER
Next, use the repair option, like REPAIR_ALLOW_DATA_LOSS, with DBCC CHECKDB command as given below:
DBCC CHECKDB (N ’admin54’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
Using the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS, can help you repair and resolve complex issues in SQL database, but it has certain shortcomings like:
- Sometimes, it can take a lot of time to execute due to factors like database size, issues on the disk where tempdb is placed, MAXDOP settings, etc.
- It can lead the database to a logically inconsistent state.
- It does not guarantee complete data recovery as it may deallocate the corrupt pages.
Method 3 – Use a Professional SQL Recovery Tool
If the pages in the SQL database are severely corrupted, the DBCC CHECKDB command may deallocate the pages, thus causing data loss. This is where a SQL database repair tool, like Stellar Repair for MS SQL can help. It can scan and repair the corrupted SQL database elements, with complete integrity. Also, it helps you recover all the data including keys, indexes, and stored procedures from the damaged MDF/NDF file and allows you to save them in multiple formats. Also, it supports repairing of database files created in SQL Server 2019, and lower versions, with ease.
Conclusion
The unexpected SQL Server/database crash can lead to data corruption. In this article, we have discussed the methods to repair and restore corrupt MS SQL database after server crash. If the recent backup is available and is readable, then you can restore the database from the backup. If the backup file is corrupted, you can use the DBCC CHECKDB command to repair the database. The quickest and best method is to use a professional SQL repair tool, such as Stellar Repair for MS SQL to recover the database within minimal time.
