How Can You Recover SQL User Database Contents from a “Suspect” Status? Here Is An Answer!

A possible scenario with SQL Database is - At startup, SQL Server tries to get a lock on the device file. If the device is used by some other process (say for example – the backup software) or if the file is missing, you might encounter this problem that your SQL database is in “Suspect Mode.” This can be a grave problem putting your crucial data at stake. To prevent such severe SQL data loss, there is a process that you can try yourself. Just follow the following steps:
1) Firstly, ensure that the device file is available.
2) Use supplemental stored procedure sp_resetstatus to reset the status of a suspect database.
For SQL Server versions 6.0 and 6.5 – if this is not done already, then create procedure by executing the Instsupl.sql script (found in the Mssql\Install directory).
For SQL 7.0 and later versions – Create it at installation by the inscat.sql script (found in Mssql\Install directory).
3) Run sp_resetstatus in the master database for the suspect SQL database:
use master
go
exec sp_resetstatus your_database_name
4) Stop and start SQL Server again.
5) Confirm that the database was recovered and is available.
6) Run DBCC NEWALLOC, DBCC TEXTALL and DBCC CHECKDB.

Now, if you fail to recover the contents from SQL suspect database even after trying these steps, then there is another way to get back you crucial data. You can try using a third-party software tool to recover SQL database contents. One such tool is SysTools SQL Recovery software which is an easy-to-use tool which you can use for SQL suspect database recovery as the software can repair corrupt MDF files and recover SQL database contents in almost all types of corruption cases.

MDF File Header Page is Corrupted – A Process of SQL Data Retrieval That You Can Try

A Possible Problem with SQL Server
This is a possible situation that while working with MDF files of SQL Server, you might encounter this problem where your hard disk is containing a database transaction log (*.ldf file) failed. On using sp_detach_db stored procedure, database can be de-attached successfully from the SQL Server Enterprise Manager console. After trying to replace the hard drive and running the command - sp_attach_single_file_db for specifying the primary data file, corruption still prevails and you encounter following error messages:
• Msg 945, Level 14, State 2, Line 1
Database database Name? cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
• Server:Msg 5105, Level 16, State 10, Line 1
Device activation error. The physical file name 'path to primary data file' may be incorrect.
• Server: Msg 945, Level 14, State 1, Line 1
Database your 'database name' cannot be opened because some of the files could not be activated.
• Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database ’your database name'. CREATE DATABASE is aborted.

If such corruption occurs in SQL Server 2000, the following error messages appear:
• Server: Msg 945, Level 14, State 2, Line 1
Database 'your database name' cannot be opened because some of the files could not be activated.
• Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'your database name'. CREATE DATABASE is aborted.

A Process of SQL Data Retrieval That You Can Try Yourself
Here is a procedure that you can try yourself to retrieve your SQL data after such corruption. In this scenario, the pre-requisite steps are Step 1) De-attach suspect database and Step 2) Replace failed hard drive (which are also described above). Make sure you have completed these steps first. Now, follow the following steps:
a) Shift the de-attached file to a safe location
b) Delete suspect database by SQL Server Enterprise Manager console
c) Make new database with same logical name and physical *.mdf and *.ldf file names as in suspect database and place all files in same directories as suspect database
d) Stop the services of SQL Server
e) Change a valid *.mdf file with suspected one (created in step 1 and 2)
f) Start SQL Server services
g) Run SQL Server Query Analyzer and then run the following script:
use mastergo sp_configure 'allow updates', 1reconfigure with overridego (Use ‘allow updates’ option to specify if direct updates can be made to system tables).
h) Run the following script:
Select status from sysdatabases where name = 'your database name'
i) Run the following script:
update sysdatabases set status= 32768 where name = 'your database name'
j) Restart SQL Server services
k) The database you trying to recover should be visible in SQL Server Enterprise Manager console (having ‘emergency mode’ status). Enter the database and SELECT the data or use BCP to get it out. (You can encounter errors while doing this, but in most cases a large part of the data can be regained.)
l) Run the following script:
dbcc rebuild_log ('your database name', 'full path to a new transaction log file')
m) After this command, SQL Server should sow an output: Warning: The log for database 'your database name' has been rebuilt. Pay attention that DBCC REBUILD_LOG is undocumented and unsupported SQL Server command.
n) Run the following script:
use 'your database name'go sp_dboption 'your database name', 'single_user', 'true'go dbcc checkdb ('your database name', repair_allow_data_loss)go
DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS clause performs:
[list=1]
o) Execute the following script:
update sysdatabases set status= 0 where name = 'your database name'
Execute
DBCC CHECKALLOC ('your database name')
and
DBCC CHECKDB ('your database name')
to verify database integrity.
p) Execute the following script:
sp_dboption 'your database name', 'single_user', 'false'go use mastergo sp_configure 'allow updates', 0go
[/list]

This process can be performed in the case where primary database file contains logical errors (which cannot be recovered even by DBCC commands).

One Final Suggestion: This procedure is somehow the last chance to recover SQL data. After this, you might damage your database. Usually database backups help to solve these tricky problems. In the case, where all these efforts and procedures fail to recover your SQL data, then one viable solution in that case would be to use a third-party MS SQL data recovery tool to repair corrupt MDF and to recover corrupt SQL database contents. One simple and intuitive software solution for MDF recovery is SysTools SQL Recovery tool. It can repair corrupt SQL database and perform SQL data recovery in almost all types of corruption scenarios.

FILESTREAM Corruption in SQL – A Phenomenal SQL Database Recovery Solution!

The process of storing and managing unstructured data was poor, prior to SQL Server 2008 release.

Earlier Approaches of Storing Unstructured Data
Before the release of SQL 2008, there were two approaches of storing unstructured data. One approach was of storing data in a VARBINARY or IMAGE column. This had transactional consistency and also reduced data managing complexities, but it was performance wise. The other approach was to store unstructured data as disk files and to store the file location in the table along with some structured data linked to it. This approach was good in terms of performance, but did not ensure transactional consistency.

FILESTREAM Feature – Efficient Storage of Unstructured Data
FILESTREAM feature was introduced with SQL Server 2008 for storing and managing unstructured data efficiently. This feature allows storing of BLOB data (like word documents, music file, image files, videos etc) in the NTFS file system. It ensures transactional consistency between the unstructured data (stored in NTFS) and the structured data (stored in table).

FILESTREAM Corruption – Error 7904
Sometimes, when you try to restore MS SQL 2008 database (MDF files) from transaction log backups, database gets damaged. You fail to perform restoration and thus MDF files become inaccessible resulting in data loss. Under such problem, you might encounter the error: “FILESTREAM corruption - missing files, error 7904.” At that time, if you want to regain access of your mission critical MDF files, you must perform SQL database recovery process using an appropriate MDF file recovery solution.

Original Database Might Not Be Corrupted
The database corrupted when you tried to restore the database from transaction log backups but the original database, from which the backup of transaction was taken, do not damages in most of the cases. In most of these cases, corruption happens on the database that was restored from a sequence of backup logs. The original database might not corrupt. “7904 16 2 Table error: The FILESTRWEAM file for "FileID" was not found.” You can get back your valuable data using a fine SQL database recovery solution to recover corrupt SQL database contents from corrupt MDF files.

A Phenomenal SQL Database Recovery Solution
SysTools SQL Recovery tool is perhaps the easiest and most efficient SQL database recovery tools available around. This MDF file recovery software performs an extensive scan of damaged databases to recover all database items like tables, reports, forms, triggers, stored procedures, etc. If you choose to repair SQL DB and recover corrupt SQL database using SysTools SQL Recovery tool then you will be gifted with a very simple interface which won’t require you to have any prior technical skills to execute the SQL recovery process. The software also has a read-only nature which helps to regain original contents of SQL database. In short, SysTools SQL Recovery software is a phenomenal SQL database recovery solution.