RECOVER Database In RECOVERY Mode & SQL Server Restore Error 3183 : How to Fix?

By far the most common search engine query leading to the blog is about fixing a suspect or unrecovered database. The very best way to do this is to use your backups, and to have a backup strategy that allows you to recover in the smallest amount of time and with no data loss. But what if you don’t have a backup for some reason? Well, it depends what’s damaged in the database and when the damage is noticed.
There are three states the database can be in when its damaged:

1. ONLINE
   • If it’s one of the data files that is damaged and the damage is hit during a query or some other operation then the database will remain online and accessible.
2. RECOVERY PENDING
   • The database will be in this state if SQL Server knows that recovery needs to be run on the database but something is preventing recovery from starting. This is different from SUSPECT because there’s nothing to say that recovery is going to fail – it just hasn’t started yet.
   • An example of this is when the database wasn’t cleanly shut down (i.e. there was at least one uncommitted transaction active at the time the database was shut down) and the log file has been deleted.
3. SUSPECT
   • The database will be in this state if the transaction log is damaged and it prevents recovery or a transaction rollback from completing.
   • Some examples of this are:
     • When the database wasn’t cleanly shut down and recovery tries to read a corrupt data page or comes across a corrupt log record.
     • A regular transaction rolls back and tries to read a corrupt data page or comes across a corrupt log record.

You can check the state of a database in the sys.databases catalog view:

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'master'; GO

or by using the DATABASEPROPERTYEX function:

SELECT DATABASEPROPERTYEX (N'master', N'STATUS');GO

Beware however, as DATABASEPROPERTYEX returns SUSPECT when the database is RECOVERY PENDING or In Recovery as you cant able to Restore/Backup

 I’ll show you below.

 Solution:
DB State is IN RECOVERY Mode due to Transaction Files not recover or still recovering
So after Truncate DB is in normal mode kindly see below

 

 
So the state the database is in determines what you can do if you don’t have a backup. The easiest case is when it’s still ONLINE. In this case you can probably run repair to remove the damage, most likely with some data loss (see my previous post on REPAIR_ALLOW_DATA_LOSS for more details), and then take steps to prevent the damage occurring again. If repair can’t fix all the errors then your only option without a backup is to extract as much data as you can into a new database.

The other two database states are more difficult and are what’s causing people to search for help. In this case the database isn’t accessible at all, because recovery hasn’t run or completed and so the database is in an inconsistent state. It could just be logically inconsistent (e.g. a transaction modifying data hasn’t recovered) or worse it could structurally inconsistent (e.g. a system transaction modifying index linkages has’t recovered). Either way, SQL Server wants to prevent you from getting into the database because it doesn’t know what state the data and structures in the database are in. But if you don’t have a backup, you need to get into the database, no matter what state things are in.

You can do this using EMERGENCY mode. In versions prior to SQL Server 2005, EMERGENCY mode wasn’t documented and you had to hack the sysdatabases table to get a database into it (worse still, the exact hack to use was changed from version to version in a bid to obfuscate things). In 2005 though, EMERGENCY mode was documented and proper syntax added to support it. Members of the sysadmin role can put the database into EMERGENCY mode using:

ALTER DATABASE [foo] SET EMERGENCY;GO

Once in EMERGENCY mode, the database is accessible only by members of the sysadmin role. The database is also read-only as nothing can be written to the transaction log.
Let’s see an example of this. I’m going to create a database and a sample table:

CREATE DATABASE [emergencydemo]; GO USE [emergencydemo]; GO CREATE TABLE [salaries] (     [FirstName]    CHAR (20),     [LastName]     CHAR (20),     [Salary]       INT); GO INSERT INTO [salaries] VALUES ('John', 'Williamson', 10000); INSERT INTO [salaries] VALUES ('Stephen', 'Brown', 12000); INSERT INTO [salaries] VALUES ('Jack', 'Bauer', 10000); GO

I’m going to start an explicit user transaction and update a row in the table:

BEGIN TRANSACTION; GO UPDATE [salaries] SET [Salary] = 0 WHERE LastName = 'Brown'; GO

Now I’m going to force the data page holding the updated row to be written to disk:

CHECKPOINT; GO

So we have an active, uncommitted transaction that’s modified the table, and the table modification has been written to disk. If the power failed at this point, crash recovery would run and the transaction would be rolled back. I’m going to simulate this by shutting down SQL Server. In another connection:

SHUTDOWN WITH NOWAIT; GO Server shut down by NOWAIT request from login ROADRUNNERPR\paul. SQL Server is terminating this process.

I’m also going to simulate damage to the transaction log by just deleting the log file.
Now when I start up SQL Server again, we see the following in the error log:

2007-10-02 11:39:47.14 spid18s     Starting up database 'emergencydemo'. 2007-10-02 11:39:47.46 spid18s     Error: 17207, Severity: 16, State: 1. 2007-10-02 11:39:47.46 spid18s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\emergencydemo_log.LDF'. Diagnose and correct the operating system error, and retry the operation. 2007-10-02 11:39:47.60 spid18s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\emergencydemo_log.LDF" may be incorrect. 2007-10-02 11:39:47.60 spid18s     The log cannot be rebuilt because the database was not cleanly shut down.

The database wasn’t cleanly shut down and the transaction log isn’t available so recovery couldn’t run. The final message is interesting – there’s a feature in SQL Server 2005 that if you attach or start up a database without a transaction log file, and the database was cleanly shut down, SQL Server will create a new log file automatically. In our case that can’t happen though.
What happens if I try to get into the database?

USE [emergencydemo]; GO

Msg 945, Level 14, State 2, Line 1 Database 'emergencydemo' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

So what state is the database in?

SELECT DATABASEPROPERTYEX (N'emergencydemo', N'STATUS'); GO

returns SUSPECT. But checking the sys.databases table using

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'emergencydemo'; GO

returns RECOVERY PENDING. This is what I’d expect, as recovery didn’t get a chance to even start.
Now I’ll set the database into EMERGENCY mode so I can get in and see what state things are in:

ALTER DATABASE [emergencydemo] SET EMERGENCY; GO

In the errorlog you can tell when a database has been put into EMERGENCY mode:

2007-10-02 11:53:52.57 spid51      Setting database option EMERGENCY to ON for database emergencydemo. 2007-10-02 11:53:52.59 spid51      Starting up database 'emergencydemo'. 2007-10-02 11:53:52.62 spid51      The database 'emergencydemo' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.

Let’s try using the database again:

USE [emergencydemo]; GO

This time it works. What’s the state of the data?

SELECT * FROM [salaries]; GO

FirstName            LastName             Salary -------------------- -------------------- ----------- John                 Williamson           10000 Stephen              Brown                0 Jack                 Bauer                10000

It’s inconsistent, as I’d expect.

That’s the catch with EMERGENCY mode – you can get into the database but recovery hasn’t run or completed so you don’t know whether the database is logically or structurally consistent. However, at least you can get into the database to extract data out or repair any damage.

SQL Server Restore Error 3183 : How to Fix?

The Scenario is..
Suppose you have a full backup (.bak file) of a SQL Server 2008 database, with partitions and you want to restore this backup file but you are unable to perform the restore operation and you are getting the error msg as shown following. Prompted Error Message
Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (8481:553819295) in database "db_name" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Microsoft SQL Server Error 3183

When you try to restore/verify the SQL backup (.bak file) you may have found this error some time because the backup may be corrupted. You have maintained the full backup of the SQL Server database and now you are trying to restore it’s BAK files on the different SQL Server database versions.
Sometimes in middle of the process, suddenly SQL Error 3183 is encountered and prompts Restore detected an error page in the database.

Reasons Behind the SQL Error 3183

The occurrence of the Microsoft SQL Error 3183 is common for database administrators. The SQL Server restore Error 3183 mainly arises due to the pages of the BAK files which have got damaged. There are multiple reasons of SQL error 3183. Here I have listed some common reasons that are responsible for this error:

1. The very first reasons is that the backup file that you want to restore has got corrupted or damaged.
2. The viruses or malfunctions can also harm the .bak file and it may be the reason responsible for the Error 3183.
3. Over storage or we can say the unavailability of free space on temporary disk can also raise the SQL error 3183.
4. Hardware failure or installation errors can raise lead to the 3183 in SQL Server database.

How to Fix SQL Restore Error 3183?

Suppose you have not accessed your original database from a long time and your only copy of the particular database is corrupted. Then the best option in this scenario is to use the RECOVER DATABASE ... WITH CONTINUE_AFTER_ERROR command. This will attempt to complete the restore past some errors. Your database will be left in a SUSPECT state if the restore actually encounters errors. From this state, you can attempt to repair the database, or extract as much data as possible from it. Some times the manual methods get failed due to highly corrupted database backup file or if the database have gone in suspect mode. At this time it is recommended that you should take the help of third party software.

NOTE:

It is recommended that if any error occurs in the database, then perform the DBCC CHECKDB command to check out what reasons are causing the error.

Ref:
http://sqltechtips.blogspot.in