Are you Fishing for an effective tactic to reverse the damaged and corrupted SQL database? Then This article has got your back. All you have to do is dig into this guide to find your SQL Recovery solution.
What made your SQL database corrupted?
Potential reasons that can source corruption into your SQL database are not limited to one. Numerous reasons can make you lie in need of SQL recovery. Have a look at these rationalities-
- Hardware issue- facing page-level corruption? Well, that can be primarily because of damaged hardware that can hamper the whole storing function.
- Power cut- we know how electricity is! A sudden cut in its supply can be held accountable for SQL corruption.
- Corrupted files- not to miss viruses and malware that can corrupt almost anything, even files under the SQL database.
- Bugs- bugs are the thugs! SQL database can be damaged because of underlying bugs.
The guide to fix the damage-
A database from a damaged MDF file in the SQL server can be easily restored by flexible DBCC check command support. It is the sole solution and technique that can repair a corrupted database in SQL Server. This write-up will follow two procedures for SQL recovery. Let’s start!
1- By fixing SQL Server database into the mode of recovery pending-
- Set database into the emergency mode by typing given query in SSMS: ALTER DATABASE [db_name] SET EMERGENCY
- Set up SQL database into multi-user mode with the help of the following query:
ALTER DATABASE [db_name] SET MULTI_USER
- In the next step, detach the database. For detaching, you need to follow the following command:
EXEC sp_DETACH_db ‘[db_name]’
- Pin MDF file
Finally execute this command to fix the recovery pending status of database-
EXEC sp_attach_single_file_db @db_name = ‘[db_name]’,
@physname = N ‘[mdf_path]
Repairing SQL server databases labelled as “Suspect or Corrupted”: When databases are marked as “suspect,” it is potentially because of a damaged or corrupted filegroup. For the recovery of the suspected database DBCC check command is suggested to use. Ahead, you will be guided through the procedure to fix suspect mode error:
- Stage1- Firstly, put the suspect database into emergency mode (by sysadmins only). The emergency mode will enable system admin read-only permission to the database. To set the system database in emergency mode, enter the following command-
ALTER DATABASE DATABASE_NAME SET EMERGENCY
[Note: enter database_name as “testing” and run the above-stated query]
- Stage2- to Examine the damage level, you have to enter the DBCC CHECKDB (DB_Name) command and analyze errors available.
- Stage 3- Namely, there are three repairing modes offered to bring the database back into functional mode from the emergency mode-
Repair_fast: this mode is suitable for backward compatibility. That means SQL server 2005 can be used in SQL Server 2008. To resolve backward compatibility issues, assure the database is in “single-user” mode rather than EMERGENCY. To convert the database into single-user mode, execute the given command in SSMS:
ALTER DATABASE DATABASE_NAME SET SINGLE_USER
Repair_Rebuild: this repair command is preferred because of its low risk of data loss. It executes all the repairing procedures that a “repair fast mode” does, and also it performs some tedious repairs, including rebuilding the indexes. To put it in use, enter the following command (in single-user mode):
DBCC CHECKDB (N ‘database name’, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS;
This mode is not just limited to repairing! It also carries out allocation and De-allocation of the pages, fixes any page error, deletes the corrupted object, and fixes structural database errors [Warning: This might lead to some data loss]. Though, you can run it in emergency mode using:
DBCC CHECKDB (N’Database_Name’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;GO
Stage 4- in this stage, you need to set the database into Multi-user mode after you have successfully repaired the database. Use: ALTER DATABASE DATABASE_NAME SET MULTI_USER
In the end-
Though the positive result is not guaranteed or assured but still worth a try, we wish you lots of luck!