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

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

Repair & Restore Master Database in MS SQL Server

In MS SQL Server Database, all the information’s are stored in a master database file. These information/data includes the instance-wide metadata like, linked servers, system configuration settings, endpoints and some more. Furthermore, master database file of SQL server records the creation of other SQL database, initialization information and location of the files for SQL Server. Thus, MS SQL Server database can not start if master database file is corrupted, damaged, or missed. This is the very critical situation for the database administrator may be business loss to recover MDF file need SQL recovery software.

Microsoft SQL Server database provides facility to checking the integrity for both logical as well as physical of all objects. DBCC CHECKDB is the facility to check the integrity of SQL server database in a specified database. This DBCC CHECKDB contains three modules- DBCC CHECKALLOC, DBCC CHECKCATALOG and DBCC CHECKTABLE, these all are used to perform specific operations in SQL server database.

Sometimes DBCC CHECKDB command does not handle the SQL server database corruption in all cases and thus your master database file (MDF) may still inaccessible. This type of problem normally occurs in case of severe corruption to the SQL server database. Take a practical scenario where you try to run DBCC CHECKDB module on master database of SQL Server and get error message like given below:

"Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'master', index 'syscolumns.ncsyscolumns' (ID 3) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:425:11) with values (id = 1794105432 and name = 'COLUMN_NAME' and number = 0 and id = 1794105432 and colid = 4 and number = 0) points to the data row identified by (). CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'syscolumns' (object ID 3). repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (master.dbo.syscolumns )."

At that situation, you will need to use advanced SQL database recovery software to get your master database file backup. Stellar Phoenix SQL Database Recovery application is able to repair and restore corrupted master database file of SQL server. It can recover stored procedure, triggers, views, collation and many more of the Microsoft SQL database.

»»  Read More...