Clear all Your Doubts About SQL Database Recovery Terms

Recently, When I was participating in forums & Q&A sites then I noticed that lots of users  have so many doubts  about SQL Database Recovery terms. 

As we know in SQL Database recovery we have these three options which we use commonly:

• Restore from backup
• DBCC Command & Repair command or 
• Third Party SQL Database Recovery Software

So, I would like clear most of the doubts related to above three options:

Is “REPAIR_ALLOW_DATA_LOSS” command not cause data loss:  It all totally depends on corruption level. When you are using this command, you should make up your mind that you can also lose you data. That's why the option is named that - seriously.

Can we run repair without running DBCC CHECKDB: No, we can’t. Repair is an option to one of the consistency-checking commands (DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKDB

Note: DBCC CHECKFILEGROUP and DBCC CHECKCATALOG don't support repair

Is Repair fix every SQL Database corruption: No, it don’t fix, Its all depends on corruption level. There are some errors that DBCC CHECKDB cannot fix.  For more detail please check this post. 

Repair is safe to use on system databases: No, it’s not safe. We should never use it on master or tempdb because they cannot be put into single-user mode.  

Is 'REPAIR_REBUILD' resolves every SQL Database corruption: No it’s not true. REPAIR_REBUILD only fixes problem in nonclustered indexes. 

Is EMERGENCY mode repair always helps: No, it’s not repair. If there is something broken in the file-system than the command get fail. 

Can we roll back repair command?: It depends. You can roll back everything if you started an explicit transaction. But users do it very rarely.

Note: EMERGENCY mode repair can never be rolled back.

Is SQL Database Recovery Software safe to use: YES, when you are using this software, you should first make a backup of your database. I always first preferred restore from backup solution so I will also recommend you it as a first step. You should only use this option when you don't have backup.  

Before using any recovery step, you should be properly aware about their pros & cons.  It will help you to choose better solution to recover your corrupt SQL Server database. 

»»  Read More...

Getting to grips with unexpectedly SQL Server Database Corruption

Data is the most valuable thing for any organization even big or small organizations. It is important for you to be ready to face if there is some data loss take place due to any reason. If you are already prepare in advance then you will face less loss to your business, A smart thinker always think about future, So you have to be ready too before any unwanted situation occur which creates unfavorable impact to your database.

 
You can easily protect your database against simple data loss scenarios but the most extreme data loss scenarios can only be solve by proper planning. So here are two main steps to handling data loss scenarios:


1) Take Full Backup of the database: Full Backup contains the database copy at the time the backup finished. You can take full backup either by using SQL Server Management studio or by T-SQL.


Benefits of Full Backup

• Full backups are completely independent from any other backups.
• These backups supports all recovery models(Full, Differential, Bulk-Logged)

2) Full Backup is sufficient for those users who have read only database. But if your database is involving many transactional activities then Full backup is not sufficient for you. At this time, you should take Transaction log backup. You can restore a database to any point of time failure by restoring log backup. But you have to underline one point to be able to restore your database by using log backup that you must be using the Full Recovery Model.
 

Benefits of Transaction Log backup

• It Supports recovery of individual transactions.
• It Supports Recovery of all incomplete transactions.
• Rolling back a restored SQL Server database, file, filegroup to the point of failure.
• It supports high availability SQL Server solutions such as database mirroring, and log shipping.

 
Additionally, there are various High Availability Solutions introduced by Microsoft, including, Log Shipping, Database Mirroring and Replication; which you can use to handle SQL database corruption issue or a database failure issue. The SQL Recovery Software also helps users to recover corrupt SQL Server database. It works even you don't have full backup, log backup of your database. It also works when high availability solutions fail to repair corrupt SQL Server database. 

 
Hope this post will help you to handle SQL database corruption. Any comments will be highly appreciated!!!

»»  Read More...

Why Stellar Phoenix SQL Recovery becomes a first choice of the users

The user conversation: Questions and Answers:

All the questions comes in users mind with these words “WHY What WHO WHERE WHEN, How?? To better understand about the software, how it works?? and why to choose it?? here are some answers to such  questions that many user may have in their mind to ask:

 

Why You prefer Stellar Phoenix SQL Recovery Software?
As per my opinion the best software is one that makes iTunes accessible to blind. It means I am recommending best software that is perfect for novice user as well for techi one. I have checked the software interface which is very easy to understand, each & every point mention in the software make it user friendly.. I highly appreciate this software is compatible with all the latest version of SQL Server & Windows. 

 

Have You find any pitfalls in it?
This is more a personal opinion: If my calculations are correct, for heavy database it performs slow recovery. I understand that it is a tough task because the recovery scanning process of SQL database in vast mode with various SQL queries to get maximum recovery of SQL database is a tough task. I wish in the coming version it become fast so that it become best software for SQL Recovery.

 

What narrowing your focus that makes it unique with other one?
I do not think any other software is “the new choice of mine”!! I know that Stellar is irreplaceable, but On the other hand I also want to say, It is better to add some other great features in new version to make it more effective with other one.

 

This is all we know right now, just over an hour of the client conversation.

 

Useful Note for every user: It is high recommendation to check user manual & demo version of the software before purchasing it. Demo version will show you that all your SQL data is recoverable by the software or not. User manual have full information about the software from A to Z.

»»  Read More...

Consequences of Error 8962 in SQL Server 2000 and steps to perform SQL Recovery

MS SQL Server has been a cynosure of every database user because of its streamlined installation, high performance, and security features. You can easily reduce the size of your database using its highly advanced, transparent compressions algorithms. It also enables data encryption without having the need to change or modify programs. A range of built-in data mining, disk partitioning, and data management tools make data storage and retrieval a breeze for the end user. Despite these benefits, SQL databases are subject to corruption. Corruption may occur due to several reasons, such as virus infection, application errors, operating system malfunction, etc. Due to this, all our vital database objects become inaccessible. To overcome this problem, you need to go for SQL recovery through a reliable third-party utility.   

For instance, while using an SQL Server 2000 database, you may come across the following error message:

“Table error: The text, ntext, or image node at page P_ID, slot S_ID, text ID TEXT_ID has incorrect node type NODE_TYPE.”

Cause:

 

The above error occurred because a database page contains a text node having invalid or incorrect type. This usually happens when there is corruption in the database.

 

This might also result in complete database inaccessibility. A recent/updated backup would help you restore all your lost or inaccessible data. However, if the backup is unavailable, follow the below mentioned resolution steps.

 

Resolution:
 

The following steps may help you resolve the issue-

• You need to check the error logs to get information about any hardware related issues. If there is a hardware malfunction, run hardware diagnostics or try replacing the damaged hardware component.  
• Use DBCC CHECKDB to fix logical database corruption.

 

If the problem still persists, you should take help of competent SQL database recovery software. These third-party tools use various safe and comprehensive techniques to repair corrupt SQL databases and retrieve almost every piece of valuable information from your corrupt database. 

 

Stellar Phoenix SQL Recovery is a proficient utility that repairs and recovers corrupt MDF files of MS SQL Server 2008, 2005, 2000, 7.0. Furthermore, the software is compatible with Windows 7, Server 2008, Vista, Server 2003, XP, and 2000.

»»  Read More...

Discussing repercussions of database corruption in SQL Server 2005 and adequate measures of recovery

Image via Wikipedia

MS SQL Sever 2005 unleashes the most advanced database management features that effectively cater to all the needs of a normal or an advanced database user. It has a new XML data type to support XML integration. You can develop custom applications for your server using SQL Server Management Objects (SMO). The most important feature is its dynamic configuration. It allows you to change the configuration values and let the changes take affect as soon as they are implemented. Despite having such powerful features, an SQL Server 2005 database gets easily corrupt due to various reasons. The primary reasons responsible for database corruption are virus infection, hardware errors, application errors, abnormal system shutdown, etc. As a consequence, you lose access to all the valuable objects in the database. To overcome this problem, you need to go for SQL recovery through a reliable third-party utility.

 

For instance, you may receive the following error message while using an SQL Server 2005 database:   

 

“Cannot find a table or object with the name 'NAME'. Check the system catalog.”

 

Cause:

The above error may occur due to any of the following reasons:
 

• You have not specified the correct object name.
• You have dropped or deleted the object before issuing the query.
• You do not have appropriate permissions to access the specified object.

 

Another reason could be that the specified object is damaged or corrupt. This would possibly render the database object inaccessible and lead to loss of valuable information. In such circumstances, you may use a backup to restore the lost data. However, if the backup is unavailable, follow the below given resolution steps.   

Resolution:

The following steps may help you resolve the problem-

• Check whether you are using the correct database.
• Verify if the specified object exists in the database.
• Also check whether you have specified the correct schema name for the object.
• Check whether the system tables contain information about the object. For that matter, you can query the 'sys.objects' catalog view.

If the problems still persists, you should take help of proficient SQL database recovery tool. These third-party software are capable of safely restoring all vital SQL database objects in any event of database corruption or inaccessibility. They perform in-depth scan of the corrupt database and also allow you to save the desired objects at your specified location in the system. Moreover, they are compatible with Windows 7, Server 2008, Vista, Server 2003, XP, and 2000. 

»»  Read More...

How to Resolve Error-824 in SQL Server Database

Error 824 is a logical IO error. Logical IO error means that the page is read from the disk successfully, but there is something wrong with the page. The error-824 caused due to many reasons, some are:

• Problem with underlying storage system.
• The hardware or a driver that is in the path of the I/O request.
• There are inconsistencies in the file system.
• The database file is damaged.
• File system corruption.

Here is more explanation with a live example of error-824:

 

Msg 824, Level 24, State 2, Line 1.
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7532c420; actual: 0x320e4240). It occurred during a read of page (1:342) in database ID 15 at offset 0x00000020e24000 in file ‘D:\Data\SomeDB.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

A logical consistency error is a clear indication of actual damage and frequently indicates database corruption caused by a faulty I/O subsystem component.
 

It can be fixed without data loss, you only need to refer to your backups and perform a restore.
 

Follow these simple steps, if you don't have updated backup:
 

• Stop SQL Service.
• Copy and Save your corrupted SQL Server database.
• Now Start SQL Service
• Create an empty SQL Server database with the same name.
• Detach the empty database and Replace it with the corrupted ones in the SQL instance folder or where you keep your databases.

After applying the above steps run the query given below:
 

EXEC sp_resetstatus 'yourDBname' ;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname ')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('yourDBname' , REPAIR_ALLOW_ DATA_LOSS) ALTER DATABASE yourDBname SET MULTI_USER

 

In case the following procedure won't work then it is recommended to go for third party SQL recovery software to resolve Error-824 in SQL Server Database.

»»  Read More...

5 Easy steps to fix SQL Database Corruption Error in MS SQL Server database

Are you facing sql database corruption error messages, when you work on MS SQL Server database? The SQL database corruption occurs due to several reasons such as virus infections, software corruption, improper system shutdown, Hardware malfunctioning etc. In such instances, user should be use in-built SQL repair inbuilt utilities.

Take a live example of an error message that occurs after SQL database corruption

“Table error: Page P_ID, slot S_ID, column C_ID is not a valid complex column.”

The error code of this error message is Error 8960 which occurs due to several reasons.

Some main causes of this issue are:

• SQL database corruption
• Table records get corrupt
• hardware corruption issues

5 main keys to resolve this error message:

1. Run DBCC CHECKDB: First run  DBCC CHECKDB command to check database errors. If DBCC CHECKDB reports database corruption error then correct it immediately otherwise you lost your data.

2. Use DBCC CHECKTABLE command to repair corrupt table records:
   DBCC CHECKTABLE ('tablename', REPAIR_ALLOW_DATA_LOSS)

3. Correct hardware-related corruption issues by performing the following actions:

• First check the error logs in MS SQL Server to verify if there is any hardware corruption or not.
• Swapping the hardware components to check the actual cause of sql database corruption.
• Reinstalling the OS after formatting the hard disks.

4. Restore corrupt SQL database from updated backup: If the problem is not hardware-related then you should restore your corrupt database from updated backup.

5. If you are still getting the same error message the you should try  Third-party SQL Database Recovery Software to repair corrupt SQL database. These software repairs corrupt mdf files as well as ndf files.

»»  Read More...

How to resolve "Nonrecoverable I/O error occurred"

Here I am sharing some experience on SQL Server database. While I am performing I/O during a backup or restore operation, I am facing this error message:

“Nonrecoverable I/O error occured on file D:\MSSQL...MDF, Backup database is terminating abnormally.”
If you want full clarification of this error message, keep reading this article:

Root Cause: This is not necessarily that this error occurs due to SQL Server Backup problem. This error is a server side error & have numerous causes such as when system do not have enough space on disk to store the backup , low-level I/O problem or it sounds like bad stripes on the hard drives. This is very bad for SQL Server database mdf file may get suspected or corrupted!

Here is full clarification to rectify this error message:

• First run DBCC CHECKDB on the database to see what you get for output. It finds 0 errors.
• After that check out that the backup medium has sufficient space or not.
• Check that you can connect to the SQL server database or not. If you can't, it is a low-level I/O problem. It is best to discuss this problem with a system administrator.  
• If this error occurs due to mdf file corruption, use these steps to troubleshoot the issue:

1. Create a new & blank SQL database with whatever name and filename that you want.
2. Generate the scripts of all SQL database tables and create them in the new SQL Server database.
3. Transfer all the data into the new SQL Server database.
4. Now check out that the same issue occurs with the new database or not. If the issue does not occur any more, you can detach the old SQL Server database, and rename the new SQL Server database to the original name to test.
5. If the same issue persists again then you should use third party SQL Recovery tool to repair corrupt mdf file.

»»  Read More...

How to resolve error: “mdf is not a primary database file”

I want to share my personal experience on SQL Server Database. One day I was trying to set my SQL database online with command:

ALTER DATABASE mydb SET online

& suddenly I got an error message:

Msg 5171, Level 16, State 1, Line 1
E:\Data\mydb_log.ldf is not a primary database file.
Msg 5171, Level 16, State 2, Line 1
E:\Data\mydb.mdf is not a primary database file.
File activation failure. The physical file name "E:\Data\mydb.mdf" may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database 'mydb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

After that I do research on this error & I found this error occurs in many situations like :

1) When you are trying to attach your SQL database files with windows then also you get same error message. Mostly this error occurs in SQL Server 2005.

2) When you are trying to set your database online then also you get same error message.

Cause: 

The main cause of this error is that your mdf file get corrupt.

Solution: 

There is four solutions to resolve this error:

1. First you should check the database by this command “DBCC CheckDB”. It shows the details of the database then you can find the problem & easily resolve it.

2. You can tried to add the file using the command sp_attach_single_file_db. 

3. Try to restore database from a bak file.

4. It is possible that the database may have been corrupted when it was downloaded & “DBCC CheckDB” reflects damages in SQL server MDF file. You also don't have updated bak file. In this case you should try SQL Recovery Software. 

Note: If data get overwrite no one can repair it. 

• When you find that your mdf file has been corrupt, don't perform any activity on the file. If there is 80% chances to resolve this error it will becomes 10%. because may be your data get overwrite from your activities & you will never get back your data. 
• Before purchasing any SQL recovery software, first download its demo version & check the preview of your recovered database after that purchase it.

»»  Read More...