Search

Friday, February 18, 2011

SQL Server Database Corruption When you Set the Database Online

Microsoft SQL Server RDBMS (Relational Database Management System) enables you to take your database online and offline as per your requirements. You can set your database either online or offline easily using the 'Alter Database' command. If MDF (Master Database File) of your SQL Server is online, all the connected users can access it, modify it, perform other operations, depending upon the privileges assigned to them. However, in some situations, you may face critical database corruption problems, while trying to set an MS SQL Server database online. This behavior leads to significant data loss situations and needs SQL database recovery to be sorted out.

As a practical example of the above problem, you may come across the underwritten error messages when you try to set your MS SQL Server database online using 'ALTER DATABASE mydb SET online' command:

Msg 5171, Level 16, State 1, Line 1 E:\Data\mydb_log.ldf is not a primary database file. Msg 5171, Level 16, State 2, Line 1 E:\Data\mydb.mdf is not a primary database file. File activation failure. The physical file name "E:\Data\mydb.mdf" may be incorrect.

Msg 945, Level 14, State 2, Line 1 Database 'mydb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

After the above behavior, database can not be set online and it remains inaccessible. To sort out the problem, you need to identify causes of this issue and then perform SQL Server recovery by fixing it.

Cause:

This behavior of Microsoft SQL Server occurs if the MDF or Master Database File is damaged and it is unreadable. SQL Server is unable to recognize the file and access its contents. Corruption may occur due to virus infection, improper system shutdown, and application malfunction like situations.

Solution:

In order to recover SQL database in such situations, you are recommended to run DBCC CHECKDB utility. You can also try restoring the database from update backup. If both of the methods fail to work, repair and restore the database using third-party applications.

The MS SQL recovery software are designed to methodically scan entire database and extract all inaccessible and damaged data from it. They have read-only conduct and rich graphical user interface to offer safe and easy recovery in all database corruption scenarios.

1 comment:

  1. Fix database from corruption & smoothly repair SQL database by using SQL repair tool. You can repair tables, views, triggers, stored procedures etc which are stored in MDF file with this tool. http://www.sqlrecoverysoftware.net/sql-server-mdf-file/

    ReplyDelete