SQL DATABASE RECOVERY

SQL Server is a Microsoft product mainly used to manage and store information. Technically, SQL Server is a Relational Database Management System. Microsoft SQL Server allows you to store and retrieve a huge amount of data by applying queries to the database. Query is nothing but a set of statements which executes specific functions when executed.

SQL also provide different types of services. Some of the SQL services include- reporting services, analysis services, replication services, full-text search service, integration service and notification services. Though these services are not essential for the operation of database system but they provide value added services on top of the core database management system.

If you have work with SQL Server, you must have heard the terms MDF, NDF and LDF already. Those are the frequently used file name extensions in SQL Server for the Primary Data files, Secondary data files and Log files respectively.

In SQL Server, data and log information are never stored together in the same file. Moreover these individual files are used by only one server.

Primary data files (MDF)

 

 A primary data file is the beginning point of the database. Every database has one primary data file. Also, all the data in the database objects such as- tables, stored procedures, trigger, views etc. are stored in the primary data files only. The most common and recommended file name extension for primary data files is “.mdf“.

Secondary data files (NDF)

You can only have one primary data file for a database and the others  file created  comes under secondary data files. But it is not necessary to have a secondary data file, so some databases do not have any secondary data file. It is also possible to have multiple secondary data files for a single database. “.ndf” file extension is usually recommended for secondary data files. It’s also possible for you to store the secondary data file on a separate physical drive.

Log files (LDF)

Log files in SQL Server databases keep all the log information that can be later used to recover and retrieve the database. The size of the log file is determined by the logging level you have set up for your database.  There is at least one log file for each database. It is also possible to have more than one log file for one database. The file name extension for log files is “.ldf“.

But these files are prone to corruptions. There is a probability  that your files  may get damaged due to corruption.

My primary objective to write a blog post on “SQL Database Recovery” was to make you understand main reasons behind corruption and  provides you an easy solution to recover your database from corruption. Some of the reasons are deletion of any SQL objects such as- views, stored procedures, index, tables, etc.   Corruption of database can also occur due to damaged LDF file, SQL database in suspect mode, virus and many other hardware/software bugs.

Sometimes executing DBCC CHECKDB and DBCC DBREPAIR commands can repair your corrupted MDF file of SQL Server. The DBCC is nothing but a set of command for checking the logical and physical consistency of a database. DBCC CHECKDB and DBCC DBREPAIR commands are a new query in MS SQL Management Studio.

The statements which are included in these commands are stated below:

ALTER DATABASE database_name SET EMERGENCY

DBCC checkdb (database_name)

ALTER DATABASE database_name SET SINGLE_USER with ROLLBACK IMMEDIATE

DBCC DATABASE (database_name, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE database_name SET MULTI_USER

Sometime the degree of the corruption may be severe and this manual solution might not be a successful approach. So if your MDF file is highly damaged, then you have to go for some other method. So, the main question which arises is – How to retrieve data from highly corrupted MDF File?  Best option to retrieve your vital MDF and other objects of SQL Server is to restore from a good Backup file. Click here to learn a simple way of restoring SQL Server backup file.

There are lots of third party recovery software which will help you to get rid of this problem. But which software to choose for an efficient and reliable solution is the area of major concern. Choosing trusted software is bit difficult as there are lots of third party solutions available in the market.

MS SQL Database Recovery is a highly innovative tool which swiftly scans and recovers your data from corrupted and damaged MDF file in no time and allows you to save your recovered data in a new database file. This tool offers high accuracy recovery of file objects such as- tables, programmability, views, triggers, default and etc.

SQL Database Recovery software supports all popular version of Microsoft SQL Server. It is also compatible and smoothly runs under all the major versions of Windows Operating System.

Some onscreen errors that pop up in the corrupted Microsoft SQL database

• Error Message824 in SQL Server: SQL Server detected a logical consistency     based I/O error.
• Error Message7105 in SQL Server:Error message occur when Large Object (LOB) data referenced by a database page row cannot be accessed.
• Error Message 5180 in SQL Server:Could not open File Control Bank (FCB) for invalid file ID.
• Error Message 605 with SQL Server:Attempt to fetch logical page (1:224) in database 11 failed.
• Error Message 2575 in SQL Server:The Index Allocation Map (IAM) page (1:1816008) is pointed to by the next pointer of IAM page.
• Error Message 2576 in SQL Server:The Index Allocation Map (IAM) page (3:443009) is pointed to by the previous pointer of IAM page.
• Error Message 8909 in SQL Server:Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1811712) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).The system cannot self repair this error.
• Error Message 8998 in SQL Server:Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 11 pages from (1:1811712) to (1:1819799). See other errors for cause.
• Error Message 8905 in SQL Server:Extent (1:1785120) in database ID 11 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
• Error Message 825 (read retry) in SQL Server.
• Error Message 832 (constant page has changed) in SQL Server.
• Database consistency errors reported by DBCC CHECKB.
• Error Message 3414 and a failed database recovery with SQL Server.
• Database System table and user table corruption Errors.
• Database page level corruption Errors.
• Clustered and non clustered Index Corruption Errors.