What is database, reasons of database corruption and how to prevent database from corruption ?

A database consists of an organized collection of data for one or more uses. Database programs are designed for users so that they can add or delete any information needed. The structure of a database consists of rows and columns of information. Companies use databases to store and organize important information, such as inventory, customer information, sales, employee records and many more. There are many programs available that enable your company to design a database to the needs, including dBase, Foxbase, mssql database etc.

There are two main types of database; flat-file and relational.

flat-file database is also called single file database. flat-file is very rarely used database because it stored small amounts of data that needs to be human readable or edited by hand.

Relational databases or structured databases are the most commonly used database today and is uses table to store information. The relational databases such as MYSQL , Microsoft SQL Server and Oracle, have a much more logical structure in the way that it stores data.

The data in Databases manage by DBMS.(database management system)

A DBMS is a system application that helps the use of integrated collection of data records and files known as databases .DBMS is a collection of data that enable user to define, create and maintain the database and provide controlled access to the database. It allows organizations to place control of database development in the hands of database administrators ( DBAs ) and other specialists .A DBMS allows users and other software to store and retrieve data in a structured way. The DBMS is very secure that only authorized users can access to confidential database information.

Advantages of DBMS:-

• Sharing of data
• Control of Redundancy
• Data Consistency
• Improved data standard
• Better data security
• Higher data integrity
• Balance of conflicting requirements
• Faster development of new applications
• Control over concurrency
• Backup & restore

Database Management System:There are innumerable numbers of DBMS software available in the market. Some of the most popular ones include Oracle, IBM’s DB2, Microsoft Access, Microsoft SQL Server, MySQL,FileMaker,Dbase,Foxpro.

Files supported by DBMS:

1. MS Access: It supports mdb and accdb files.
2. MYSQL: It supports .myd and .myi files.
3. Oracle: It supports .dbf files.
4. SQL Server: It supports .mdf , .ndf and .ldf files.
5. Dbase: It supports ,.dbt ,.ndx .dbf files.
6. Foxbase: It supports .dbf file.

The problem comes when Your database get corrupted and You are not accessing your data. There are many reasons of database corruption:

1) PC crashing when a database is open.

2) Improper system shutdown when a database is open.

3) Virus infection.

4) Operating system corruption

5) File system damage.

And many more...

How to prevent data from corruption:

Set aside any old backups of the database—DO NOT overwrite them. Then, make a new backup of the database and put it in a safe place. And you can access your corrupt data.

This is the solution when you take backup of your database. If you dont take backup than how you access your corrupt data than you can use third party software.Many software are available in market which recover your corrupt data after all you don't take backup of your data. These software are:

• easy to use.
• user-friendly.
• used effective scanning methods and restores the repaired files at your desired location .
• Repair files after all kinds of logical corruption.

Fixing Error 8947 by recovering damaged MS SQL Server 2000 database

An IAM (Index Allocation Map) is a page in the database that stores one bit per extent in all the GAM (Global Index Map). A set bit is used to point out that the extent is allocated to that particular index. At times, this convention is not followed as two IAM pages refer to the same GAM interval. Lot of times, this problem is caused due to database corruption, which in turn, can occur because of virus infections, database header corruption, abrupt or unplanned system shutdown, etc. There are several methods that can be used to achieve MDF file recovery. However, if you fail to do so then you should use a third-party SQL recovery software to do the needful.

Let us assume a situation in which you are getting the following error message while working on the MS SQL Server 2000 database:

“Table error: Multiple IAM pages for object ID O_ID, index ID I_ID contain allocations for the same interval. IAM pages P_ID1 and P_ID2.”

Cause:

The root problem of this error is that the IAM chain for the specified index has two IAM pages, P_ID1 and P_ID2, that cover the same GAM interval.

Resolution:

To overcome from such situation, you should perform the following methods, preferably in the given order:

• Addressing the hardware-related problems: First of all, you should look at the hardware-related issues that may have caused the database corruption. Some of the measures that you can follow are:

  • Checking if write-caching is enabled on the hard disks. If yes, then you should get the hard disk replaced from your local hardware vendor.

  • Fixing any error that appear after you run hardware diagnostics.

  • Checking the Application error logs to ensure that the problem is not caused from hardware malfunctioning.

  • swapping the hardware components to isolate the actual error of corruption.

• Restoring from backup: You should check the status of the backup. If it is clean and updated, then you should restore the database with the backup.

• Running DBCC CHECKDB: You should repair SQL database using DBCC CHECKDB tool with the suggested repair clause.

If you still get the same error, then you should recover MDF file with the help of a third-party SQL database recovery software. These tools use highly advances scanning mechanisms to safely recover database without any modifications.

SQL Database Recovery Using SQL Recovery Tool

Tips to resolve “Possible bad chain linkage...” Error

Allocation of MS SQL Server table data can be done either in B-tree or in heap structure. Data retrieval from a heap structure is slow as compared to retrieval from B-tree structure, which is very fast. B-tree structures minimize the navigation of your record, thereby resulting in fast retrieval of your data. B-tree structures use leaf nodes as storage components to save table records. Records saved in MS SQL Server table become inaccessible if these leaf nodes are missing, damaged, or not pointing to the correct parent node. In most of these situations, a database user encounters an error message. To overcome such error messages and to access table records, users can restore data from an updated backup. However, if no backup is available, then the user needs to opt for SQL Database Repair solutions. To elaborate the above scenario, consider a realistic situation, wherein, you encounter the below error message while trying to access table records: “Table error: Object ID O_ID, index ID I_ID. The next pointer of P_ID1 refers to page P_ID2. Neither P_ID2 nor its parent were encountered. Possible bad chain linkage.” The error message does not allow you to view records saved in the table. Note: This is a severity level 16 error message. The above error message appears when a page (P_ID2) referenced by page (P_ID1) goes missing. Such situations arise when your SQL Sever database is logically or physically damaged. To surmount the above error message, you first need to identify the exact cause of database corruption and then act accordingly. Below mentioned steps help you in identifying and repairing the SQL database corruption: * To identify a hardware problem, you need to check the system, the application, and logs of SQL Server. After the damaged hardware is detected, you will need to swap it with a new component.

How to fix Error 8929 when text node gets corrupt in MS SQL Server 2000 database

In an MS SQL table, a text node is used to save the data that is stored in the rows of the records. Every text node contains a parent node and is formulated in either a B-tree or a heap format. At times, when the text node becomes damaged then you are unable to access the data in the tables. Such problems can occur due to various issues such as virus infections, hardware malfunctioning, database header corruption, etc. You should perform appropriate workarounds to repair MDF filefailing which you can try a third-partySQL repair software to repair SQL database.

Taking a practical scenario into account in which you are getting the following error message while working on an MS SQL Server 2000 database:

“Object ID O_ID: Errors found in text ID TEXT_ID owned by data record identified by RID.”

Cause:

The primary cause of this problem is that there is corruption in a text node. Usually, Error 8962 and/or 8963 also appear with this error message.

Resolution:

To resolve this error, you should try to perform SQL recovery. This could be done using the following methods to repair SQL file, preferably in the given order:

• Look for hardware related errors and resolve them: It has been seen that most of such errors appear due to failed hardware parts. You can carry out the following measures to address such issues:

◦ Fixing the errors that appear when you run hardware diagnostics

◦ Checking the error logs and analyzing if any of these occurred due to corruption in the hardware components

◦ Swapping the hardware components to isolate the exact cause of error

◦ Checking if write-caching is enabled in the disk. If yes, then contact your local hardware provider for the solution.

◦ Reinstalling the operating system after formatting the hard disks

• Use the database backup: You should restore the database with its backup if it is clean and updated.
• Run DBCC CHECKDB: You should run DBCC CHECKDB with the appropriate repair clause.

You would be able to repair MDF file using the aforementioned methods. However, if it is not so then there is a serious need to take the services of a third-party MDF file repair software. Immaculately designed user interface and the use of highly advanced scanning mechanisms are some of the best features of such.MDF repair tools.

Count Occurrences of Characters in a String – SQL Server

Count the occurrences of characters in a string, stored in our SQL Server Database.

DECLARE @longWord varchar(28)='Thisissqlanddotnetdevelopment'

SELECT CountCharacters = DATALENGTH(@longWord)
- DATALENGTH (REPLACE(REPLACE(LOWER(@longWord),'a',''),'i',''))

When should you use a third-party SQL recovery software to fix Error 8959 in MS SQL Server 2000 database

The IAM (Index Allocation Map) pages are an important part of SQL databases. The IAM pages enable indexing that results in faster searching and modifying operations. All these pages have a distinct index that should have the same index ID. However, that is not the case when the database is corrupt, which stops you from working on the database. In these circumstances, you should apply different methods for MDF recovery. If these methods do not work, then you should urgently seek the services of a third-party MS SQL Server recovery software.

Consider a scenario in which you are getting the following error message while working on the MS SQL Server 2000 database:

“Table error: IAM page P_ID1 for object ID O_ID1, index ID I_ID1 is linked in the IAM chain for object ID O_ID2, index ID I_ID2 by page P_ID2.”

Cause:

The root cause of this problem is inconsistency in the IAM pages, which is caused due to database corruption. In this case, one of the IAM pages linked into the IAM chain for I_ID2 index has I_ID1 on it.

Resolution:

To rectify this situation, you should recover MDF file. For this purpose, you can employ the following methods preferably in the given sequence:

• Address hardware-related corruption issues: First of all, you should check whether the problem has occurred due to hardware malfunctioning. This can be done using following methods:

  • Check Windows application logs and SQL Server Error logs to ascertain if the error has caused due to faulty hardware parts or not.

  • Fix the errors that appear after running hardware diagnostics.

  • Exchange hardware parts to isolate the actual error of corruption.

  • Reinstall operating system after formatting the hard disks.

• Replace database with updated backup: If the database backup is clean and updated, then you should replace the database with its backup.

• Run DBCC CHECKDB: You should repair the damaged MDF file with the help of DBCC CHECKDB tool with the suggested repair clause.

The problem would be solved easily in similar cases. However, if the database is severely damaged, then you should use a third-party master database file recovery software to repair the database. These SQL recovery tools use fast and sophisticated scanning algorithms that enable quick, easy, and safe database recovery.

Troubleshooting Error 8908 in a damaged MS SQL Server MDF file

At times, the MS SQL Server 2000 MDF file on which you are working gets damaged due to various issues such as power outages, corruption in the database header definitions, etc. Because of the MDF file corruption, you are unable to carry out important tasks on it or it may become totally inaccessible. In such cases, one should look to address the corruption issues. If the error remains unsolved, then I would recommend you to use a professional MDF repair software to repair MDF file.

Let us take a scenario in which you get the following error message while working on the MS SQL Server 2000 database:

“Table error: Database Id DB_ID, object Id O_ID, index Id I_ID. Chain linkage mismatch. P_ID1->next = P_ID2, but P_ID2->prev = P_ID3.”

Cause:

Such behavior is caused by corruption in the header definitions of the database.

In this case, the next page pointer of P_ID1 page points correctly to the P_ID2 page. However, the previous page pointer of the P_ID2 page is pointing to P_ID3 page instead of the P_ID1 page, which is incorrect.

Resolution:

To overcome such database corruption scenario, you should repair MDF file using the following methods:

• Rectify hardware corruption issues: Most of the MDF corruption issues are caused due to faulty hardware parts. You can resolve them by performing the following tasks:

  • Running hardware diagnostics: You should run hardware diagnostics and try to eliminate the errors by fixing them.

  • Analyzing error logs: You should check the error logs and see if any error has occurred because of problems in the hardware components. Try to fix them, if any.

  • Checking for enabled write-caching: You should check if the hard disks are enabled for write-caching. If yes, then contact your local hard disk vendor to replace the hard disk.

  • Reinstalling operating system: If nothing else works out, then you should format the hard disk and install the operating system.

• Restore from backup: You should restore the database from its backup, if it is updated and clean.

• Run DBCC CHECKDB: Run the DBCC CHECKDB tool with the suggested repair clause to repair SQL database.

After using these MDF file repair methods, try to reproduce the steps that caused the error last time around. It would not appear if the MDF file is repaired properly. In case, you are still getting the error then you should get the services of a third-party .MDF repair software. These tools repair SQL database without causing any damage to the already corrupted MDF file by using totally non-destructive scanning mechanisms.

Check If Stored Procedure Exists, Else Drop It and Recreate – SQL Server

IF EXISTS(SELECT * FROM dbo.sysobjectsWHERE id = object_id(N'[dbo].[YourStoredProcName]')and OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[YourStoredProcName]GOCREATE PROCEDURE dbo.YourStoredProcNameAS-- Logic Comes HereGO

Update: A BETTER solution suggested by Madhivanan

IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1DROP PROCEDURE [dbo].[YourStoredProcName]GOCREATE PROCEDURE dbo.YourStoredProcNameAS-- Logic Comes HereGO

The syntax shown above will drop a stored procedure if it exists and recreate it.

Temp Table VS Table Variable in SQL Server

Here are some differences between Temp Table and Table Variable in SQL Server

Temp Table	Table Variable
Temp table is valid for a session. For eg: when you run the following code create table #temp(i int) insert into #temp select 345 Go create table #temp(i int) insert into #temp select 345 Go you will get an error	Table variable has a statement-level scope. ie as soon as you execute the statement the scope is lost For eg: when you run the following code declare @t table(i int) insert into @t select 45 GO declare @t table(i int) insert into @t select 45 GO you will not get an error
It is possible to alter the temp table to add columns, idexes,etc	It is not possible to alter a table variable
It is possible to truncate a temp table	It is not possible to truncate a table variable
SELECT INTO method can be used for temp table SELECT * INTO #temp from your_table	SELECT INTO method cannot be used for table variable. You get error for the following SELECT * INTO @t from your_table
Temp table can be useful when you have a large amount of data	For small set of data, table variables can be useful

How to troubleshoot the Error 7906 in damaged MS SQL Server 2008 database

MS SQL Server 2008 is an RDBMS (Relational Database Management System) from Microsoft that is used to provide enterprise solutions. It is fast, reliable, secure, and scalable to name few of its salient features. However, as nothing is perfect in this world, it can get corrupt due to few internal as well as external factors. These corruption issues can be sorted out using some corrective methods. If none of these succeed, then the use of a third-party SQL recovery software is the best suggestion that I can give.

A similar situation may occur while you work on the MS SQL Server 2008 database in which you get the following database error:

“Database error: The file 'FILE' is not a valid Filestream file.”

Cause:

There is a 'File' file in the Filestream dataspace that is found in it whereas in ideal circumstances few specific files such as 'filestream.hdr' are found under it.

Resolution:

To resolve this erroneous situation, one must work out some method to perform SQL Recovery. Some of these methods can be followed in the given order:

• Address the hardware-related corruption issues: Quite often such issues are caused by faulty hardware parts. For this, you should perform the following measures to recover the damaged database:

  • Swap the hardware parts in order to check and focus on the actual reason of database corruption.

  • Run hardware diagnostics and try to eradicate whatever errors are resulted.

  • Check the error logs and see whether some problem has occurred due to faulty hardware parts.

  • Check if there is write-caching enabled on your hard disk controller. If yes, then contact the hardware vendor to replace the hard disk.

  • If nothing works out, then perform a reinstall of the operating system after the hard disk format.

• Replace the database from the updated backup: However, if the hardware is safe and sound then check and analyze the status of the database backup. If it is up-to-date, then replace the database with the clean and updated backup.

Now, try to check if the error appears again. If the problem is still persisting, then you should use a third-party SQL database recovery software to recover the damaged database. Using these non-destructive SQL recovery tools ensure that the recovery is safe and secure.

Different ways to secure the SA Login

The SA account is created during the installation process and the SA account has full rights in the SQL Server environment. The SA account is well known and often targeted by malicious users, so it is advisable to disable the sa account unless your application requires it.

You can use the following ways to secure the SA login.

---

Use Windows Authentication Mode

One way of doing this is to use “Window Authentication mode” from the Server Properties dialog which will allow access to only Windows logins and not SQL logins. Microsoft also recommends Windows authentication mode only. One thing to note is that even though you are using Windows authentication, you must use a strong password for the SA account, because anyone can change the authentication mode by updating the registry value and restarting the SQL Services.

To check your current setting you can use these techniques outlined in this tip.

To change this setting in SQL Server Management Studio, right-click the server, click Properties and go to Security page as shown below.

---

Disable the SA Login

Disabling the SA account is a good option to prevent its use. When it is disabled no one can use it in any circumstance until it is enabled. The only disadvantage is that we can’t use the SA account in an emergency.

You can use the below T-SQL to disable SA account.

--Query to disable the SA account. ALTER LOGIN sa DISABLE; 

This query will check the status of the SA account. A value of 1 indicates the account is disabled and 0 indicates the account is enabled.

--Query to check account status SELECT name,is_disabled from sys.server_principals where name='sa'

---

Rename the SA Login

You can also rename the SA account which will prevent hackers/users to some extent. The one disadvantage is that it does not change the SID for the SA account which by default is 0x01, so someone could find the new name by looking up the SID.

--Query to check account status ALTER LOGIN sa WITH NAME = [mssqltip];

This query could be used to lookup the name for the SA account based on the SID.

SELECT * FROM sys.syslogins WHERE sid = 0x01

Ref: http://www.mssqltips.com

Different ways to get Identity of New Inserted Rows in SQL Server

There are different methods to know the Identity Value of a newly added row.

Let us consider the following example:

--Create a Table

CREATE TABLE test(id int identity(1,1),names varchar(100))

--Insert Data

INSERT INTO test(names) SELECT 'testing'

--Get Identity Value that is Populated in the Current Scope

SELECT scope_identity()

--Get Identity value that is Populated in the Current Session

SELECT @@identity

--Get Identity value that is Populated in the Table

--Regardless of Scope and Session

SELECT ident_current('test')

Note that first two methods won’t give correct values if data are added to the ‘different tables’.

How to fix Error 7995 in corrupt MS SQL Server 2008 database

SQL database recovery

Lot many times you face problems while using the MS SQL Server databases. These problems may, at times, disable you from using the database. Or, in worse cases the database may become totally inaccessible. These problems are the result of database corruption, which can be, of course, solved by using various recovery techniques. However, if these techniques do not prove adequate then I would suggest you to use a professional SQL database recovery software.

Let us consider a similar situation in which you are facing the following error message when using the DBCC CHECKNAME tool in MS SQL Server 2008 database:

“Database 'DBNAME': consistency errors in system catalogs prevent further DBCC CHECKNAME processing.”

Cause:

Such problem can occur because of the following issues:

• The system tables are damaged.

• The system tables are saving all the metadata of all the database objects.

Resolution:

To overcome such problem, you have to recover MDF file, which you can do by using the following methods. Please ensure that you perform these methods one after the another, that is, if one fails then try the next one.

• Address the problems in hardware parts: In most cases, such problems are caused by faulty hardware components. You can address these problems by:

  • Checking the error logs and rectifying the problems.

  • Checking if write-cache is enabled on the hard disks. If yes, then contact the local hardware vendor to replace the hard disk.

  • Switching the hardware parts to see if the error is appearing again.

  • Installing the operating system again after formatting the hard disks.

• Check the status of backup: If the hardware parts are in acceptable condition, then analyze the status of the backup. If it is up-to-date, then restore the database with the backup.

• Run DBCC CHECKDB: If the backup is not up-to-date, then run the DBCC CHECKDB tool with the appropriate repair clause. This tool will inspect the errors to check what repair can be done for each of these.

If these methods are insufficient to recover SQL database, then also you need not worry as there are various third-party SQL database repair tools that can be used in such situations. The best thing about these tools is that they perform MDF recovery without any damage to the existing database.

What to do when DBCC CHECKDB is unable to recover Error 5242 in MS SQL Server 2008

Inconsistencies in MS SQL Server databases cause serious problems like making the database inaccessible, which could further result in data loss. Such problems can appear because of sundry reasons but the most prominent ones could be infected from viruses, unplanned system shutdown while the database is open, database header corruption, etc. Such problems could be solved using various methods. But, if nothing works out then the use of third-party master database file recovery tools is advised to recover SQL database.

One such scenario appears when you receive the following error message while working on an MS SQL Server 2008 database:

“An inconsistency was detected during an internal operation in database '%.*ls'(ID:%d) on page %S_PGID. Please contact technical support. Reference number %ld.”

Cause:

Such mentioned problems are encountered when there is some sort of inconsistency in the structure of the database page. The same is reflected in the error message itself.

Resolution:

In such situations, the following methods are recommended to recover SQL database:

• Address hardware related problems: You should check the hardware components to see whether this problem is because of any problem with the hardware. Some of these measures could be:

  • Running hardware diagnostics and checking the error logs.

  • Interchanging the hardware components to zero-in on the actual reason of database corruption.

  • Formatting the hard drive and installing the operating system again.

• Restore from backup: If the hardware components are alright, then examine the backup status. If the backup is updated, then replace it with the problem database.

• Use DBCC CHECKDB: If both of the previous methods do not work, then you should use the DBCC CHECKDB command, first without repair clause to know the extent of corruption. Then, you should use it with the recommended repair clause.

If these solutions do not provide MDF file recovery, then you require a third-party master database file recovery software to recover SQL database. These SQL recovery tools are read-only in nature that use highly refined mechanisms to safely extract data from the corrupted databases.