When a SQL Server database is configured on RAID, failure of the RAID drives can corrupt the data and lock you out of the database. In such a situation, you must replace the failed drives and try to rebuild the RAID array, and restore the database from backups.
Caution! Rebuilding the RAID array can cause data loss. This may happen if you rebuild RAID with a missing drive, different configuration, different stripe size, etc. So, make sure to take precautionary measures before RAID rebuild to prevent any data loss. These include imaging each drive from the RAID before a rebuild, restoring backups to another volume, avoiding deleting any additional files, etc.
If the rebuild process fails, but you're able to recover the data files (MDF and NDF) from the failed RAID, you could attempt to reattach the data files.
Methods to Recover SQL Database after RAID Failure
Here are some effective methods that can help you restore the SQL database and recover data after RAID failure.
Method 1 – Restore SQL Database from Backup
Try to restore the SQL database backup file (.BAK) onto a completely different system and verify the backup. For details on database restore, see this blog: How to Restore Database in SQL Server from .Bak File Step By Step.
If you're able to restore the database, check if it passes the DBCC CHECKDB test without any errors.
Method 2 – Reattach the MDF File
Note: This method will work for users who are able to recover their database files from the RAID array.
If there is no backup, try attaching the database files, then check the integrity of those files by running the DBCC CHCEKDB command on a different (test) server. If the database files are not functional or CHECKDB returns any errors, you will need to manually repair the files. For this, attempt the steps suggested by Paul Randal on the blog: "EMERGENCY-mode repair: the very, very last resort".
The first step is to make the database accessible by setting it to EMERGENCY mode. Once the database is accessible, set the database to SINGLE_USER mode to repair the database using the DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option.
Note: The CHECKDB may fail to execute or may take a significantly longer time to complete. This is where Stellar Repair for MS SQL comes in handy. The software repairs corrupt SQL database (MDF and NDF) files and recover all the data within the files. It uses parallel processing to allow the users to repair and save multiple tables simultaneously, helping the users to perform quick database restore.
Conclusion
This blog discussed the methods to recover a SQL database after RAID failure. You can try to restore the database from a SQL backup file. If the backup isn't available, rebuild the RAID and then reattach the SQL data files (.mdf, .ldf). If rebuild fails and you're able to recover the files, you can still attempt to reattach them to a different server. Once you are able to restore the database, run DBCC CHECKDB against the database to check its consistency. If DBCC CHECKDB reports any errors, you will need to manually repair the database which can take a considerably long time to complete and can cause data loss. A better alternative is to use Stellar Repair for MS SQL software. The software repairs the database MDF file and recovers all its data, maintaining data integrity.
- Right-click 'This PC' icon on your system and click Manage.
- On the screen that opens, expand Storage and then click Disk Management.
- At the bottom of the screen, in the middle pane, you can see different disk numbers. Under the numbers, check if the word Basic or Dynamic is mentioned.
- If the word Basic is mentioned, then you don't have a RAID array configured on your system or you may be using a hardware RAID. If Dynamic is mentioned under disk numbers and each drive letter is same on all the drives, then you have software RAID set up.
- Launch the software. Select the database you want to repair by clicking the Browse or Find button.
- After selecting the database, click Repair.
- Choose a scan mode to continue with the SQL database repair process.
- Click OK when the message 'Selected MS SQL database repaired successfully' appears.
- Verify the accuracy of recoverable database objects in the preview window.
- To save the recovered data, click Save on the File menu.
- Choose the file format to save the data, including MDF, CSV, HTML, and XLS.
- Choose to save the recovered data into a New Database or a Live Database.
- Enter the details to connect to your SQL Server instance, select the location to save the database, and hit Save.
The data will get saved at the selected location.
4 min read
4 min read
4 min read
