Summary: The error 5243 in SQL Server usually occurs while restoring the SQL database. The error indicates a problem with the backup file or database file. In this post, we will discuss this error in detail and help you resolve it. We will also mention an advanced SQL repair software that can easily recover data from corrupted SQL database and backup files.
Several users, on different forums, have reported encountering the error 5243 in MS SQL Server. The error appears when restoring the backup file, during recovery after the server restart, or when checking the MS SQL Server error logs. The complete error message is as follows:
Msg 5243, Level 22, State 8, Line 1
An inconsistency was detected during an internal operation. Please contact technical support. Reference number %ld.
The above error message is not revealing much. However, Level 22 indicates the severity level, which means such errors occurs rarely and there might be some issues with the database or the disk. State 8 specifies that there is an issue with password or authentication. Let’s understand the reasons behind this error and see how to fix it.
Causes for SQL Database Restore Error 5243
There are several reasons that can lead to the error 5243 in SQL Server, such as:
- Inconsistencies in the backup file.
- Incorrect backup file path.
- The SQL database file (.mdf/.ndf) is corrupted.
- Hardware problems on the system hosting the database file.
- Issues on the Memory/IO subsystem.
- Restoring the encrypted database with invalid certificate/keys.
- The backup file is not compatible with your MS SQL Server version.
Methods to Resolve Database Restore Error 5243 in MS SQL Server
First, you can check the SQL Server error logs to find detailed information about the error 5243. The log files contain all the user-defined system events that can help you to troubleshoot and fix the issue. If the issue is not clear, then follow the below troubleshooting methods to fix the error.
Method 1: Move the Backup File to Another Folder
You may face issues when restoring the backup file, if the folder where it is saved has some issues. As a workaround, you can try moving the backup file to a new/different folder. Make sure the new folder has full read/write permissions. Once you moved the backup file, then use the new file path to restore the backup file. If error persists, follow the next solution.
Method 2: Check Backup File Compatibility
The database restore error 5243 can also occur if you are trying to restore the backup file, which is not compatible with the MS SQL Server version you are using. For example, the backup file is created in MS SQL Server version 2019 and you are trying to restore it in 2014 or an earlier version. To check the version of the backup (.bak) file, use the below command:
"RESTORE HEADERONLY FROM DISK ='<backup path>'
Method 3: Check the Certificate/Asymmetric Keys
MS SQL Server requires certificate or asymmetric key to restore an encrypted database. If the certificate is missing or if you are using the incorrect asymmetric key to encrypt the database, then you may encounter the error 5243.So, make sure you are using the correct key at the time of restoring the encrypted database. You can refer to the Microsoft document on SQL Server Certificates and Asymmetric Keys.
Method 4: Check and Correct the File Path
The error 5243 may also occur if the path of the backup file is incorrect. Make sure you’re specifying the correct file path when restoring the backup file.
Method 5: Verify the Backup File
You may encounter the error if the backup file you are trying to restore is not valid. You can run the VERIFYONLY (Transact-SQL) command (see the below example) to check if the backup file has all the required files and is in the correct format.
RESTORE VERIFYONLY FROM DISK = ‘Backup path’;
If the backup file is valid, it will display a success message. Otherwise, it will return an error indicating corruption. In this case, you can repair the corrupted .bak file by using a professional SQL database backup repair tool.
Method 6: Repair SQL Database
Several users have reported facing the 5243 error after running the DBCC CHECKDB command on the database. This indicates corruption in the database file. In such a case, you can use the repair option recommended by the command to repair the database. Here’s how:
If your database is inaccessible, then first change the database status to EMERGENCY mode. This will provide read-only access to the administrator. To change the database to EMERGENCY mode, run the below command:
ALTER DATABASE [Dbtesting] SET EMERGENCY
Next, set the database to SINGLE_USER mode to prevent other users from changing the data during the repair process. To do this, run the below command:
ALTER DATABASE Dbtesting SET SINGLE_USER
Now, use the REPAIR option recommended by DBCC CHECKDB command to repair the database.
If it recommends to use the REPAIR_FAST option, then run the DBCC CHECKDB command as given below.
DBCC CHECKDB (' Dbtesting ', REPAIR_FAST)
GO
This option only maintains backward compatibility syntax and does not perform any repair actions.
Alternatively, you can use the DBCC CHECKDB command with the REPAIR_REBUILD option. It can repair missing rows in non-clustered indexes without any data loss. Also, it can resolve only minor corruption issues in SQL database. Here’s the command:
DBCC CHECKDB (' Dbtesting ', REPAIR_REBUILD)
If the DBCC CHECKDB command recommends to use the REPAIR_ALLOW_DATA_LOSS repair option, then execute the below command:
DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
The REPAIR_ALLOW_DATA_LOSS option can help resolve maximum errors in the database but can cause data loss. It may deallocate rows or pages in the database to resolve the error, and the deallocated pages are not recoverable.
Use a Professional SQL Database Recovery Tool
If the above methods fail to resolve the SQL error 5243, there is a chance that the backup file is corrupted. In such a case, you can use a professional SQL database recovery tool, like Stellar Repair for MS SQL Technician. It can restore data from corrupt MS SQL Server database backup (.bak) files and save it in a new database file. It can also repair damaged or corrupted database files and recover all the data, even the deleted objects. The tool supports both Windows and Linux operating systems.
To Conclude
The SQL database restore error 5243 can occur due to several reasons. You can follow the methods discussed in this post to fix the error, depending on the cause. If the error has occurred due to corruption in backup or database file, then you can use a professional SQL repair tool, such as Stellar Repair for MS SQL Technician. This tool can repair corrupt SQL database files quickly and without any data loss. This MVP-recommended tool can also recover data from corrupted backup (.bak) files with complete precision. It can help in resolving all errors associated with corrupt backup and database files.
