How to fix Error 'Restore failed for Server 'SQLSERVER'

-->

Backup & restore are the most important task of any database administrator. A good backup & restore plan will help you to save your valuable data as well as business. If you have not maintained backup of your database and suddenly disaster occurs on the database then you will be in nightmare situation. If you are working for a company then you may loose your job also; therefore always maintain and update your backup properly.

Consider a scenario wherein you have two server, server A & server B and both the servers have SQL server version 10.50.1600. You have taken the backup of your data & log file from server A and trying to restore on server B but got following error message

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'SQLSERVER'. (Microsoft.SqlServer.SmoExtended)

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)

Cause: The most possible cause for above error message is that your destination server (server B) may have connected to lower version of SQL server as compared to source server (server A).

Note: Restoring a SQL server Database from lower version to higher version or same version would not give error message but restoring from higher to lower version will always result error message.

How to Fix: To fix above error message, you will have to perform below steps.

• First of all open SQL query window
• Run “Select @@Version” command
• See the Output of command
• Match current version of server B with old connected server
• If versions are different for both servers then update the version of old connected server
• Now perform restore operation
• Successfully done

»»  Read More...

How to transfer SQL database from one server to another??

I have found an interesting topic in the morning that discussed about “how to backup the database from one server to another ?”. We all want our database will always safe and corruption free. In this modern tech era every databases are very pron to the corruption. As database administrator our main task is maintain the our database safe and high availability for the users. A SQL server database got corrupted or damaged by the several reasons like metadata structure corruption, virus attack, human errors, hardware related issues and many more. In the case of corruption we can restore our database from the secondary server thats why we want to transfer our SQL database from one server to another. But it is very expensive because we will have to maintain the both servers. Most of the companies or organizations prefer their database safe rather that expenses. Microsoft provides several options from transferring SQL database from one server to another server, which are given below:

• Log Shipping
• Database Mirroring
• Snapshot Replication

Log Shipping: In this method you can not perform any operation on the database that is on secondary server. If you want your secondary database to be accessible then you will have to backup, copy the SQL server database on the network and restore from there.

Database Mirroring: In this methods also you can not perform any operation on the secondary server. It just create the copy of your SQL server database. You can use the second database in any corruption on the primary database.

Snapshot replication: If you want to transfer your database from one server to another server on the regular basis then this method is very helpful and effective. This method does not slow the SQL server performance.

At last you can also use database copy tool for the transferring SQL server database from one server to another server.

Note: The main motive behind this transferring operation is recover MS SQL database if any corruption happens at the primary database.

»»  Read More...

MDF database recovery utility to deal with Table error Extent P_ID range of this database

While working on your SQL Server database, sometimes the database tables become completely inaccessible following any corruption or damage to the database. The reason behind the corruption can be virus attack, file system corruption, application malfunction, simultaneous access by the number of users beyond the upper load limit etc. In any of these situations, the database flashes some sort of error message, which points toward a possible problem with the database. You need to resolve the error to regain access to the database tables. However, if you failed to resolve the issue, there are third-party SQL database recovery tools available, which can help you recover the database back.

The error message that you may have encountered can be read as below:

“Table error: Extent P_ID object ID O_ID, index ID I_ID is beyond the range of this database.”

After encountering the error message, you can not be able to access any record from the SQL database and receive the same error, each time you try to access any table.

Causes: The above error tells that the size of the pageinfile of the SQL database has been greater than the physical size of the filenum. Here, the P_ID is the PageID of the filenum:pageinfile form. This is the reason behind the error and may have been occurred due to a possible metadata structure corruption.

Resolution: In order to resolve the problem, you have to run the DBCC CHECKDB command with appropriate clause. This will repair the corrupt database.

If you failed to resolve the problem, you have to look for your backup server for any available valid backup. But, if you have not taken any backup recently or there occurs any error while resolving the problem, you are left with a single choice of running any mdf database recovery software.

These mdf file recovery utilities are designed with powerful algorithms to repair the database and recover various objects, including the tables, views, forms, containers etc with ease. These SQL recovery applications are completely read-only in nature and do not modify the database by any degree. Moreover, with the highly interactive graphical user interface, any user can use the software and retrieve his data without even any further assistance.

Source taken from here: http://www.articleshub.org/article/62451/MDF-database-recovery-utility-to-deal-with-Table-error-Extent-P_ID-range-of-this-database.html

»»  Read More...

How to Restore SQL Server Database When DBCC CHECKDB Fail?

We have a client who uses Microsoft Dynamics NAV with a SQL 2005 database. Six days ago they started getting consistency errors in their database. We had restore from the last updated backup, and that seemed to fix the issue.

Yesterday they again started getting consistency errors in different area of the database. After everyone was off, I ran a DBCC CHECKDB command on the database with REPAIR_ALLOW_DATALOSS (the minimum level stated in the first CHECKDB I ran), and it failed to restore.

Here is a partial list of the results;

DBCC results for 'NAVSQL'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 281498259750912 (type Unknown), page ID (3:2179484) contains an incorrect page ID in its page header. The PageId in the page header = (8192:65536).
Repairing this error requires other errors to be corrected first.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 3906872676743905280 (type Unknown), page (34749:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 46139657 and -4.
The repair level on the DBCC statement caused this repair to be bypassed.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 5641040008258584576 (type Unknown), page ID (4:675855) contains an incorrect page ID in its page header. The PageId in the page header = (256:10150144).
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 3 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 32308 rows in 186 pages for object "sys.sysrowsetcolumns".

...

CHECKDB found 0 allocation errors and 44 consistency errors in database 'NAVSQL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

At last we used stellar sql recovery software to fix the problem. Stellar SQL recovery software is an advanced tool that repair and recover corrupted sql server database. This software is compatible with window 7.

»»  Read More...

Resolving Error 8925 in MS SQL Server 2000 database using SQL database recovery software

In MS SQL Server databases, the records are stored in the form of pages, which are linked to each other in a sequential manner. Because of being linked to each other, the searching and inserting new records is made easy thereby saving a lot of precious time. However, at times the database are unable to exhibit the expected functionality because of database corruption, which can occur due to various reasons such as virus infections, hardware malfunctioning, abrupt system shutdown, etc. In these circumstances, certain in-build methods can be used to recover SQL database. If these methods do not work, then I would suggest to use a third-party MS SQL Server software to overcome such situations.

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

"Table error: Cross object linkage: Page P_ID1, slot S_ID1, in object ID O_ID1, index ID I_ID1 refers to page P_ID2, slot S_ID2, in object ID O_ID2, index ID I_ID2."

Cause:

The primary cause of this problem is that the P_ID1 page points to another, P_ID2, page in a different object.

Resolution:

You should perform the following methods to recover MDF file in these situations:

* Address hardware-related issues: A lot of times these errors are caused by malfunctioning of the hardware components. You can use the following measures in order to resolve such issues:

o Fixing the errors that appear after running hardware diagnostics.
o Checking the Windows Application log report and MS SQL Server Error log to ascertain whether the error has occured because of hardware malfunctioning.
o Checking if write-caching is enaled on the disks. If yes, then contact the local hardware vendor to get it changed.
o Swapping the hardware components to isolate the actual reason of database corruption.
o Reinstalling the operating system after formatting the hard drive.

* Restore from backup: If the hardware is alright, then you should restore database from the backup if it is clean and updated.

* Run DBCC CHECKDB: If the backup does not solve the problem, then you should run DBCC CHECKDB with the suggested repair clause.

If you still are getting the similar error message even after using these methods, then you need to repair the damaged SQL database using a third-party master database file recovery software. These SQL database recovery tools are able to repair corrupt MDF files without any damage to the existing files.

The best SQL database recovery software that restores various database objects including collations, tables, NDF files, stored procedures, etc, is Stellar Phoenix SQL Data Recovery. Supporting MS SQL Server 7.0, 2000, 2005, and 2008, this .MDF recovery software is compatible with Windows 7, Server 2008, Vista, Server 2003, XP, and 2000.

»»  Read More...

Repair & Restore SQL Server Database from Suspect Mode

Sometimes when you try to connect to your SQL database server, you may get your SQL database in suspect mode. At that point your database server would not allow you to access and perform any operation on it. Your database will accessible only when the database will be repaired. For fixing the problem you will have to use advanced SQL database recovery tool.

SQL server database can go in suspect mode for many reasons; some of them are given below:

Improper shutdown of the database server
Corruption of the database files
Unavailable device files
Unavailable database files
Database resource used by operating system
SQL Server incorrectly asserts free data page space when a row is inserted


You can know the exact reason for the SQL database suspect mode. You will have to use given below query for knowing the exact reason.

“DBCC CHECKDB (’YourDatabasename’) WITH NO_INFOMSGS, ALL_ERRORMSGS”

After running the above query you will able to know the exact reason of the database suspect mode.

To fix the suspect mode and restore the database, run following queries in Query Analyzer of SQL server database,

“EXEC sp_resetstatus ‘yourDatabasename’;
ALTER DATABASE yourDatabasename SET EMERGENCY
DBCC checkdb(’yourDatabasename’)
ALTER DATABASE yourDatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDatabasename’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDatabasename SET MULTI_USER”

At finally you have done it. But sometimes after the running of above queries in query analyzer you are still unable to perform any operation on the database. This means your database has got badly corrupted. At that situation you will have to use third party SQL server recovery software to repair the database.


Stellar Phoenix SQL database recovery tool is the advanced repair and restore tool that support MS SQL server 2008, 2005, 2000 and 7.0. It can also repair the NDF files of the database. It is comfortable with windows 7, XP, Vista, 2003, 2000 and NT.

»»  Read More...

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 file failing which you can try a third-party SQL 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
AdsSpy: 28 sites by this AdSense ID

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

The best MS SQL repair software that repairs SQL databases created in MS SQL Server 7.0, 2000, 2005, and 2008 is Stellar Phoenix SQL Data Recovery. Compatible with Windows 7, Server 2008, Vista, Server 2003, XP, and 2000, this SQL repair software repairs MDF file components such as NDF files, collations, tables, stored procedures, etc.

Resource: http://www.americanchronicle.com/articles/view/183342

»»  Read More...

How to Resolve 'Error 823' While Accessing SQL Database?

Are you getting 'Error 823' while accessing an SQL Server database or modifying the database? Are you getting various database inconsistency errors while querying SQL Server database? Well, the problem may take place if your SQL Server database is either inconsistent or damaged. In such critical situations, you must have a complete backup to restore data from it. However, the situations may become worse if there is no current backup in place. At this point of time, you have to opt for SQL recovery tools to get your valuable data recovered.

You may come across the below errors in Windows Application Event Log or MS SQL Server ERRORLOG while performing any operation on MDF (Master Database File) of SQL Server:

"2010-03-06 22:41:19.55 spid58 Error: 823, Severity: 24, State: 2. 2010-03-06 22:41:19.55 spid58 The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000a72c0000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\my_db.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level 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."

2009-12-09 04:53:49.170 spid25s Error: 823, Severity: 24, State: 2. 2009-12-09 04:53:49.170 spid25s The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000132000 in file 'D:\tempdb\tempdb_data.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level 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.

Cause:

The Error 823 may occur due to either SQL Server database corruption or I/O errors in the database. The error usually indicates that that there are some inconsistency in file system or the database file is corrupt.

Resolution:

To recover SQL server database, use any of the below methods:

Run Chkdsk to find file system inconsistency issues and resolve it.

Use DBCC CHECKDB utility to try repairing the damaged database.

If the above methods can not perform MS SQL recovery, You need to use third party applications to repair and restore the damaged database. The MDF Repair software performs safe, quick, easy, and absolute recovery in all MDF corruption situations.

For More Information Visit: http://ezinearticles.com/?How-to-Resolve-Error-823-While-Accessing-SQL-Database&id=4396378

»»  Read More...

Rectifying Error 5228 in SQL Server 2008

When you use the DML (Data Manipulation Language) commands to update the database, the rows sometimes get in an intermediate state. It is called the antimatter state that enables concurrent DELETE statements to leave a pointer for the index builder transaction so that deleted rows are not inserted again. Ideally, these rows should be deleted at the end of the operation. However, sometimes it does not happen that invariably results in database corruption. To resolve this situation, you should use appropriate corrective methods. However, if you are unable to do so then you should use a third-party MDF repair software to address the issue.

Consider a scenario wherein you are working on a table in SQL Server 2008. You get the following error message while performing some modifications on the database table:

“Table error: Object ID O_ID, index ID I_ID, partition ID PN_ID, alloc unit ID A_ID (type TYPE), page PG_ID, row R_ID. DBCC detected incomplete cleanup from an online index build operation. (Antimatter column value is VALUE.)”

Cause:

The root cause of this error message is that for the O_ID object , I_ID index, and PN_ID partition, there is an unfinished online index build was detected. This is testified by the presence and the displayed value of an antimatter column on the R_ID row.

Resolution:

To resolve this error message, you should perform the following methods, preferably in the given order:

Check for hardware issues: You should run the hardware diagnostics and check if the error appeared because of any hardware issue. You can also try to swap the hardware components to zero in and resolve the issue. In addition, you can try to reformat the disk and reinstall the operating system.


Restore from backup: Check if you have a clean and updated backup of the database. If yes, then you should restore the database with its backup.

Run DBCC CHECKDB: As a last resort, you should run the DBCC CHECKDB command without the repair clause to ascertain the level of the corruption. You should run the command again after knowing the repair clause. It will rebuild the corrupted indexes.

However, if you still are not able to resolve this error by any which method then you should use a third-party SQL recovery software. Such read-only tools are able to recover SQL database using sophisticated scanning algorithms without overwriting the original data.

Stellar Phoenix SQL Recovery is an MS SQL recovery software that enables you to recover corrupted databases created in SQL Server 2000, 2005, and 2008. It is competent enough to restore various objects of SQL database such as tables, queries, stored procedures, triggers, views, etc. This SQL recovery software is also able to preserve the database constraints and user-defined data types. It is compatible with Windows 7, Vista, Server 2003, XP, and Server 2000.

»»  Read More...

Rectifying Error 2579 in SQL Server 2000

The most basic unit of storage in an MS SQL Server database is a page, which accounts for 8 KB of size. And 8 such pages consist an extent thereby making 16 extents in 1 MB. An extent is used to allocate spaces for tables and indexes. The well-being of the database structure is absolutely essential for the database. However, at times problem arises in the structure of the database because of various reasons such as virus infection, power surges, human error, hardware failure, etc. In such cases, you should use appropriate measures to rectify the situation. However, if you are unable to do so then you should use a third-party SQL server recovery software to recover SQL database.

Consider a scenario wherein you encounter the following error message while working on an MS SQL Server 2000 database table.

“Table error: Extent P_ID object ID O_ID, index ID I_ID is beyond the range of this database.”

Cause:

The cause of this error message is that an extent has been allocated in an Index Allocation Map (IAM) page for the referenced index ID. In the error message, P_ID is the ID of the corrupted page in the table. The pageinfile of the extent is greater than the expected size of the file specified in the database, which has caused the error.

Resolution:

To resolve this error message, you can perform the following methods, preferably in the given order:

Look for hardware issues: Run hardware diagnostics to resolve any issues. Also, check the error logs to check whether the issue has occurred because of any faulty hardware. You can also swap the hardware components to zero in on the problem. You can also consider switching to a new hardware system altogether.

Restore from backup: Restore the database from its backup if it is updated and clean.

Run DBCC CHECKDB: Finally, you can run the DBCC CHECKDB command, first, without any repair clause to know the corruption level. Then, run the command again with the suggested repair clause. However, you should refrain from using this method if you are not sure about the aftereffects of using the repair clause on the database.

However, if you are unable to address the situation even after using the mentioned methods then you should use a third-party SQL Server recovery software to recover SQL database. These read-only tools are able to perform SQL database recovery using fast yet sophisticated scanning algorithms without damaging the original data.

Stellar Phoenix SQL Database Recovery is an MS SQL recovery tool that enables you to recover corrupted databases created in SQL Server 2000, 2005, and 2008. It is able to perform mdf repair of various database components such as tables, triggers, views, rules, stored procedures, etc. This SQL Server recovery tool is also capable of recovering database constraints and relationships among tables. It is compatible with Windows 7, Vista, Server 2003, XP, and 2000.

»»  Read More...