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.
