Is Your SQL Server Database Need to Repair?

Run below command to check whether your SQL server database needs to be repaired or not

DBCC CHECKDB (Database-Name) with no_infomsgs

• DBCC CHECKDB: It checks all the logical & physical integrity on the specified SQL server database.
• Database-Name: Just replace the 'Database-Name' with your database name for which to run DBCC CHECKDB integrity. If you not specified the database name then current database will be used.
• No-Infomsgs: It will remove all the information messages

If above command executed without any error message then there is no need to repair SQL server database but if there is error message(s) then you need to repair your SQL server database.

Note: If error message appears with DBCC updateusage then you need to run DBCC UPDATEUSAGE command on the database.

DBCC UPDATEUSAGE: It detects and corrects all the table related problems like rows, data page count, reserved page, used page etc. in specified database. Run below command to resolve table related problem in the database.

DBCC UPDATEUSAGE ({database_name} {table_name}) with no_infomsgs

If appears error message does not contain dbcc updateusage then you need to repair your SQL server database.

Why Need to Repair?

You can see the reasons from the Windows system & application event logs. SQL server logged possible cause for the problem in the windows system logs.

Suggestion: I will recommend you to check your database backup before repairing the corrupt SQL server database. If Backup is clean & updated then restore it.

How to Repair?

If backup is not available then you need to repair your SQL database. SQL server provides following repair options:

• Repair_Rebuild: SQL server suggests it when there is no any possibility of data loss from the specified database.
• Repair_Fast: SQL server suggests it when no repair is performed
• Repair_Allow_Data_Loss: It will try to fix all the error messages with possibility of some data loss.

To perform any of the repair options, you need to put your database in single user_mode:

ALTER Database <database-name> SET Single_User

Here is the syntax of all repair options:

DBCC CheckDB ([YourDatabase], REPAIR_REBUILD)

DBCC CheckDB ([YourDatabase], REPAIR_FAST)

DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

After repairing your database, don't forget to set your database in multiple user_mode. You can do this by running following command:

ALTER DATABASE [YourDatabase] SET MULTI_USER

What to Do after Repairing the Database?

After repairing your corrupt SQL server database, you need to do following things:

• Re-Run DBCC CHECKDB: It is very important to re-run DBCC CHECKDB command on the database after repairing. If it comes out without any error message then it is conform that there is no any inconsistency in the database.
• Make Full Backup of Database: If DBCC CHECKDB comes out cleanly then take a full backup of your database and keep it at safe place. Backup will help you in restoring of database.
• Test Your Backup: Verify your backup by restoring it on another server. Successful restorations can only guarantee that your backup is consistence.
• Make Disaster Recovery Plan for Future: Make a proper disaster recovery plan for future.

»»  Read More...

How to Restore SQL Server Database with & without backup

It is quite common in real life that sometimes when you tried to open your SQL Server database either you are unable to open your database or it gives some error message. In most of the cases it happens due to database corruption. In this case either you have updated backup or you have not. If you have full backup then follow these steps to repair corrupt SQL Server database:

Step 1: Retrieve the Logical file name of the database from backup.

RESTORE FILELISTONLY

FROM DISK = 'D:BackUpFile.bak'

GO

Step 2: Use the values in the LogicalName Column in following Step.

Make Database to single user Mode

ALTER DATABASE YourFirstDB

SET SINGLE_USER WITH

ROLLBACK IMMEDIATE

----Restore Database

RESTORE DATABASE YourFirstDB

FROM DISK = 'D:BackUpFile.bak'

WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',

MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

Without Backup:

I recently downloaded Stellar Phoenix SQL Database Recovery Software. I found many interesting things about this tool which caught my attention. The tool helps to repair SQL Server database when you don't backup. 

My Scenario:

Database get corrupt & you don't have any backup. The corruption can be just like these errors:  

My Solution:  Stellar Phoenix SQL Database Recovery Software.

Important Points to remember:

1. First Ensure that MS SQL Server is running during recovery process.

2. You should perform recovery process on the copy of your SQL Server database. 

3. First Try Demo Version of the software. 

I think this tool is very useful for SQL Server users, and it delivers a high level of performance way more than what I expected. If you are using this tool, I am eager to learn more about it and your experience while using it.

»»  Read More...

How to Repair SQL Server 2008 R2 Database From suspect mode

From last one month, I am researching on most common SQL Server database issues. I checked many forums as well as blog post & noticed that most of the SQL server database users suffer with “SQL database goes in suspect mode” issue.

 

What the exact meaning of this issue: The database goes to suspect mode due to improper system shutdown, database corruption, corrupted log file etc. You can find out the exact reason by following steps:

First run this query:

“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 80% cases, the issue gets resolved but in 20% cases the new error message get arise such as:

Warning: You must recover this database prior to access.
Msg 8921, Level 16, State 1, Line 5
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Warning: The log for database 'ServeDB' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer
has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has
been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Msg 8921, Level 16, State 1, Line 9
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
 

Note: May be you got different error message but in most of the cases the above error is same. 

Look at the Possible Solutions:

 
In this case, you cannot repair database easily by running above query, first you should understand why your database get corrupted. You can check it by running DBCC CHECKDB command. After that restore your database from updated backup - but make sure before restoring backup, it's not corrupt itself.
 

If it is happen, use SQL Database Recovery Software to repair SQL Server database. The Software works great with all latest versions of MS SQL Server including 2008 R2, 2008, 2005, 2000. 

»»  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...

Stuck with SQL Database Recovery Process? Sit Back and Keep reading this article

Image via Wikipedia

When I start SQL Server application, it starts to recover all three SQL Server database files (MDF, NDF, LDF). Due to this all transactions are either committed or rolled-back. I suffer with one another problem: The server get terminated in the middle of a long running update, Normally the recovery process takes a few minutes, but after server termination it takes plenty of time like we update SQL Server.

 

Steps that I follow:

• Give it enough time to recover SQL Server ,
• for checking what exactly happened, I  checked the current and previous errorlog files, and NT errorlogs,
• After that I check recovery progress for this I set trace flag 3412 which writes an entry to the errorlog when each transaction is rolled forward or back.

In very rare cases SQL Server is not recover the database correctly. After completing these steps until the recovery process is take time to complete. You can restore SQL server database with updated backup. If you do NOT have a backup then you can use the following trace flags to recover SQL Server database:

• If the database is still unavailable - marked as suspect - then run the following command to put the SQL database into emergency mode.

  UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
         
       Then you can access into the database and extract out the data.

• After performing all steps if you are unsure what to do, then don't hesitate to use the SQL Recovery Software provided by Stellar Phoenix. They are available 24x5 to deal with the customer problems and prevent your data with data loss.

Small Overview About the software

• Software recovers MDF & NDF Files
• Recovers almost all objects of SQL Server Database
• Recover database in most possible cause of corruption
• Support all latest version of SQL Server & Windows OS

You can check the site for more detailed information about the software. If this article is useful for you & solve your SQL Recovery issue, write your valuable comment!!!

»»  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...