A SQL Server crash may leave the database in an inconsistent and unusable state. If no database files (MDF/LDF) are lost or damaged, simply restarting a server instance can help you recover the database. But if the files are corrupted and data is lost, you will need to perform additional steps to restore the SQL Server database. In this post, we’ll discuss the steps to restore the SQL databases after a server crash. But first, let’s discuss the possible reasons behind the server crash.
What Causes SQL Server Crash?
Some common reasons why SQL Server crashes are as follows:
- Power failure or hardware (like memory disks, processor, etc.) failure on the server.
- Bugs in the SQL Server. For instance, SQL Server 2012 SP3, 2014, and 2016 may crash due to “access violation error while reading data from an event file target.”
Steps to Restore SQL Server Database after a Crash
Note: Before attempting the following steps, check for cumulative updates rolled out by Microsoft. Applying these updates can help fix bugs that might have caused the server crash. For more information, refer to KB3178107, KB3138775. If this doesn’t help, proceed with the steps below.
Step 1: Restart SQL Server
First of all, try restarting your server. To do so, open the Services application on your Windows PC and search for SQL Server Services. Right-click on services and click Start.
Once the server starts running, execute the following step.
What If SQL Server Won’t Start?
If your server won’t start, you will need to reinstall it and attach the database files.
The steps to attach the database files are as follows:
Note: These steps will only work if you’ve access to the MDF and log (LDF) files of the database you need to restore. If you can’t locate the database files or they are corrupt, skip to the next step.
- In SQL Server Management Studio (SSMS), right-click on Databases and click the Attach option.
- In the ‘Attach Databases’ window that appears, click the Add button to select the MDF file you want to attach, then click OK.
- A ‘Locate Database Files’ screen opens with the list of databases you can attach. Select a .mdf file and click OK.
- A dialog box opens with details of the associated files of the database you need to restore.
- Verify the files and click OK.
Step 2: Restore from Backup
You can try to restore a SQL Server database from a valid, verified backup. Let’s demonstrate an example to recover a database from a verified backup media set. For this, perform these steps:
- In SSMS, right-click on the database you want to restore. Then, click Tasks -> Restore -> Database.
- In the ‘Restore Database’ window that opens, you can use the ‘Verify Backup Media’ option to check if there is any problem with the backup media.
- In our case, the media is fine.
- Now rename the test database backup to ‘Test_Database_1’, then click on ‘Script.’
- The following T-SQL query is displayed in the SSMS.
We don’t need to back up the log. So, we will remove the backup log query and only restore the database. Also, we will add ‘CONTINUE_AFTER_ERROR’ to ensure that the restore runs despite any errors and restore what it can. Plus, we will add ‘NORECOVERY’ to put the database in RESTORE mode.
USE [master]RESTORE DATABASE [Test_Database_1] FROM DISK = N’C:\Test_Database.TRN’ WITH CONTINUE_AFTER_ERROR, NORECOVERY, FILE = 6, MOVE N’Test_Database’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\DATA\Test_Database_1.mdf’, MOVE N’Test_Database_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\DATA\Test_Database_1_log.LDF’, NOUNLOAD, STATS = 5 GO |
Executing the above query gives the following output.
The restore operation is now processed. Refresh the database. You could see that the new backup ‘Test_Database_1’ is in restoring state.
- Now, we need to bring the database back ONLINE. To do so, execute the query below:
USE [master]RESTORE DATABASE [Test_Database_1] FROM DISK = N’C:\Test_Database.TRN’ WITH CONTINUE_AFTER_ERROR, NORECOVERY, FILE = 6, MOVE N’Test_Database’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\DATA\Test_Database_1.mdf’, MOVE N’Test_Database_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\DATA\Test_Database_1_log.LDF’, NOUNLOAD, STATS = 5 GORESTORE database [Test_Database_1] WITH RECOVERY |
- The database is now restored successfully. Refresh it. The database is now back ONLINE.
End Note
Without tested backups, you may fail to restore the database to its original form. An alternative is to use a specialized SQL repair tool, like Stellar Repair for MS SQL that helps repair corrupt database and restore it to their original state. The software fixes corruption in SQL database (MDF and NDF) files and recovers all the database objects. It previews the recoverable data and allows to save it to an existing or a new database.
Also Read: Migrating Data from Microsoft SQL Server to Snowflake