When restoring the SQL Server database from backup, you may encounter the error 3183. It usually occurs if validation of a page fails while restoring the database backup, performed using the CHECKSUM option. Due to this, the restore operation is terminated. You may get an error message similar to this one:
Msg 3183, Level 16, State 1, Line 1
RESTORE detected an error on page (10:10) in database "stellardb" as read from the backup set.
In the above message, 3183 is the error ID. Level 16 means that it is a severe error and State 1 means that the error occurred while reading the page. Line 1 is the line where the error occurred.
Why the error 3183 occur in SQL Server?
The most likely cause for this error is that a SQL Server database page is corrupted. This can happen due to various reasons, such as hardware failure, software problems, operating system issues, etc. For example, if the hard drive sectors are damaged, then the pages get damaged. A power outage can also damage the hardware, thus corrupting the SQL Server database pages. If your operating system fails, it can also damage the database pages. Viruses and malware can also affect the database backup.
Solutions to Fix the Error 3183 in SQL Server
You can follow the below solutions to fix the SQL Server error 3183 and restore the database.
But before that, first check if your database backup is corrupt. For this, you can use the RESTORE command in T-SQL with the VERIFYONLY option (see the below example).
RESTORE VERIFYONLY FROM DISK = 'C:\Path\To\YourBackup.bak';
This command helps check the integrity of backup file.
Solution 1: Use the CONTINUE_AFTER_ERROR Option
You can try to restore the database with the CONTINUE_AFTER_ERROR option. This option will restore the database but some data may be lost. This option tries to ignore the errors and continue to restore the database.
The following example shows how to restore the database using the CONTINUE_AFTER_ERROR option.
RESTORE DATABASE [stellar]FROM DISK = N'e:\Backup\stellar.bak'
WITH FILE = 2,
NOUNLOAD,
STATS = 5,
CONTINUE_AFTER_ERROR;
Once restored, you can check the database status by running theDBCC CHECKDB commandsee the below example).
DBCC CHECKDB('stellar') WITH NO_INFOMSGS;
Solution 2: Use a Specialized SQL Database Backup Repair Software
If the RESTORE DATABASE command with the CONTINUE_AFTER_ERROR option fails or to avoid data loss, you can use a third-partySQL database repair softwareto repair the corrupt backup file. Stellar Repair for MS SQL – Technician is one such third-party software that can repair and restore corrupt database backup files of any SQL Server version. You just need to select the backup file and the software will repair it. Let us see how this software works.
First, install the Stellar Repair for MS SQL - Technician software.
Then, launch the software and select theExtract from MS SQL Backupoption.
If you know where your backup file is located, press the Browse button and select the .bak file. If you do not know where it is, you can use theFindbutton to search the backup file.
After selecting the backup file, press theScanbutton.
You will see two scan options:
- TheStandard Scan, whichis the default option and the recommended one.
- TheAdvanced Scan, whichis slower but used in case the Standard Scan fails.
Select the backup and pressNext.
You will see all your database objects in the preview window.
Press theSaveicon.
You can save the data in a New Database, Live Database, or inOther Formats,like Excel, CSV, or HTML.
How to Prevent the Error 3183?
Here are some best practices you can follow to prevent the error 3183 in SQL Server.
- Make sure to install antivirus and antimalware software.
- Always use UPS to avoid power outages.
- Test your backups on regular basis.
- 在云存储备份或者至少在一个different physical location.
- Monitor the status of your hardware.
- Monitor the status of your database.
Conclusion
The SQL Server error 3183 usually occurs due to corrupt pages. You can follow the solutions mentioned above to fix this SQL Server error that appears when restoring database backup. You can try to restore the corrupted backup using the CONTINUE_AFTER_ERROR option. If the CONTINUE_AFTER_ERROR option fails, you can use Stellar Repair for MS SQL. This software repairs the corrupt backup and restores all the data.
