Restoring SQL Server databases from .mdf filesFile Repair Tool Blog

Microsoft’s SQL Server profits all sorts of businesses, as it facilitates fast, flexible and secure record maintenance. SQL Server allows you disconnect database from main server program, to make secure backup copies or transmit data to branch offices. After disconnecting database, you must reinstate from its .MDF file using “attachment” method. SQL Server proposes two ways to attach .MDF file: via Management Studio program’s graphical interface or using typed Transact-SQL text commands.

Start SQL Server Management Studio

Step 1

Go to “Start” button then “All Programs” menu, and then find “Microsoft SQL Server” to open the record of SQL Server programs.

Step 2

To open Connect to Server dialog box click “SQL Server Management Studio”.

Step 3

Select a server on network and set the “Authentication” pull-down catalog to “Windows Authentication” and hit the “Connect” button.

Restore With Transact-SQL

Step 1

Press “New Query” within Management Studio’s toolbar to open large text area the right side of screen.

Step 2

Click the text area and type Create Database statement using Transact-SQL code shown below:
CREATE DATABASE MyDatabase ON (FILENAME = ‘c:data filesmy_data.mdf’), (FILENAME = ‘ c:data filesmy_data.ldf’) FOR ATTACH;

Step 3

Press “Execute” button in Transact-SQL toolbar, situated under Management Studio’s main toolbar. Symbol of execute is a right pointing triangle. This way SQL Server Management Studio retrieves database.

Corruption Issues of the SQL Database Files

There are several issues, which lead to.MDF and .NDF file corruption. Below you have few common issues of database file corruption.

• Hardware malfunction is one of the common issues that cause corruption of database file.
• Sudden power failure in the middle of any work
• Replication of database can also lead to data loss; it can also happen when you create triggers on system tables.
• When you rename the primary file group, it will not change but it can corrupt your database.
• File header corruption
• Storage media corruption where MDF files are stored
• Storing SQL database in compressed folders
• Accidental data deletion
• Changes in SQL Server account

The above given issues are responsible for the corruption of MS SQL database.

Manual Process to Repair Restoring SQL Server databases from .mdf files

Now, come straight to the point on how to repair corrupt or damaged SQL Server Database. There are many options to repair Restoring SQL Server databases from .mdf files. Here, you have some manual tactics to repair the corrupted files.

Note: Before getting into the manual repair process, do not forget to have a backup copy.

Method 1: Using Microsoft SQL Server Management Studio Express

Step 1: Download MS SQL Server Management Studio Express, as per your database version.

Note:

• Use MS SQL Server Express 2005, for Webtrends Analytics 8.5 and 8.5a:

http://www.microsoft.com/en-us/download/details.aspx?id=8961

• Use MS SQL Server Express 2005, for Webtrends Analytics 9.2x and 8.7d, use MS SQL Server Express 2008:

http://www.microsoft.com/web/gallery/install.aspx?appsxml=&appid=SQLManagementStudio%3bSQLManagementStudio

• Before installation Management Studio Express 2008, you need Microsoft Web Platform.

Step 2: After installing MS SQL Server Management Studio, open it by the given steps. Move to Start -> Programs -> Microsoft SQL Server 2005/2008 -> SQL Server Management Studio Express.

Step 3: Use Windows Authentication, & log into the database.

Step 4:  Now, expand the Databases and right-click on wt_sched and choose Properties -> Options.

Step 5: In the bottom of the windows modify Restrict Access to SINGLE_USER.

Step 6: Select OK button and repeat step 3 for wtMaster section of the database.

Step 7: From the toolbar choose New Query and paste the given commands in query pane.

dbcc checkdb(‘wtMaster’,REPAIR_REBUILD)

dbcc checkdb(‘wt_sched’,REPAIR_REBUILD)

Step 8: Select the Execute button to repair SQL database.

Step 9: Now, perform the steps 4 &5 and change the databases back from SINGLE_USER to MULTI_USER.

Note: Before starting, the above manual steps make sure that no Webtrends services are start.

Method 2: Using Rebuild Wizard (Rebuildm.exe)

It is the best way to repair the master database with Rebuild Wizard located in the given place:

Program FilesMicrosoft SQL Server80ToolsBINN directory.

Step 1: Double-click on Rebuildm.exe to open Rebuild Master by.

Step 2: Rebuild Master tab will open and there you will find Collation Settings and location of data files.  Select the database file & verify other information and then click on Rebuild.  

Note: Make a copy of source directory from SQL CD to your hard drive.

Step 3: Now, you will get the confirmation message. Hit on Yes button after the process gets over and you will get a message that rebuild was successful.  Now you have new master database so restore the master database easily.

Step 4: Next, start SQL Server in SINGLE USER mode by opening the command prompt and type the command sqlservr.exe –c -m from Program FilesMicrosoft SQL ServerMSSQLBINN directory.

Step 5: From backup, store master database or store it with Query Analyzer or SQL Enterprise Manager.

Step 6: After restoring, exit SINGLE USER mode and restart SQL Server in NORMAL OPERATION mode.

Note:

• If restore operation fails then rebuild the master, database and attach all of your databases using Enterprise Manager or Query Analyzer.
• InEnterprise Manager, right-click on the Database and select Attach Database

Method 3: Using DBCC CHECKDB

Step 1: Run DBCC CHECKDB on corrupt database. Follow the given query:

DBCC CHECKDB (Name of corrupt database)

Note: You can identify the options like no_infomsgs , infomsgs with DBCC CHECKDB.

Step 2: Now, view the index id.

Case 1:  If index id is, greater than one then recreate it.
Case 2: If index id is 0 or 1 then re-run DBCC CHECKDB with any suitable repair options like repair_rebuild, repair_fast, repair_allow_data_loss.

DBCC CHECK (name of corrupt database, repair_rebuild)

DBCC CHECK (name of corrupt database, repair_fast)

DBCC CHECK (name of corrupt database, repair_allow_data_loss)

Step 3: To make sure zero corruption, run DBCC CHECKDB and you will find a new message i.e

DBCC CHECKDB found 0 allocation errors and 0 consistency errors in ‘name of your corrupt database’.

Method 4: Using MS SQL Server Management Studio:

Step 1: Install and open Microsoft SQL Server Management Studio.

Step 2: Connect it with your database. Then hit on New Query button.

Step 3: In a New Query page paste the below SQL script and replace [YourDatabase] with your database’s name.

Step 4: Hit on Execute

Method 5:

Step 1: Stop SQL Server and copy your MDF and LDF files to the different location

Step 2: Now, remove the original MDF and LDF files and start SQL Server then, create new database.
Note: Use the same name and file names as your old file.

Step 3: Stop SQL Server and overwrite the newly created MDF and LDF.

Step 4: Once your database is online get into EMERGENCY mode and SINGULAR mode.

Step 5: Follow the given code to execute DBCC CHCKDB and check repaired files.

DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

Note: REPAIR_ALLOW_DATA_LOSS means few data might be lost so keep a backup copy of the files.

If all the given manual methods fail to repair Restoring SQL Server databases from .mdf files then, the last option left with you is to use a third party MS SQL Database Repair Tool. This tool is effective and helpful in repairing the highly corrupted SQL database.

MS SQL Database Repair Software

MS SQL Database Repair Tool is the simple and efficient tool that can easily repair corrupt database file. It can completely recover your .MDF files including functions, tables, views, store procedures, triggers, constraints, indexes, keys, rules, defaults and many more. It uses an advanced algorithm to recover the files without damaging its original content. This program meets all the requirements of users. It is developed in such a way that user can easily install and repair their SQL (.MDF) Files with an ease. It is effective and very simple to use.

This tool is capable of repairing all sort of corrupted files including sudden power failure, over sized issue, hardware and software defect, and human errors. Simply, download this tool to enjoy its amazing features.

Features of SQL Database (MDF) Repair Software

• It can repair corrupt .mdf database files, views, tables, stored procedures, defaults, rules, user defined data types and triggers from MS SQL server database.
• It can repair and restore SQL Server database files (*.mdf or *.ndf).
• It can recover objects in MDF and NDF database files
• It can even recover triggers, indexes, tables, keys constraints, rules and defaults.
• It can effectively recover large SQL Server database.
• Restores SQL Server ROW-compressed and PAGE-compressed data
• It shows preview of recoverable objects before recovery
• Allows Selective Recovery
• Support Microsoft SQL Server 2014, 2012, 2008 and all previous versions.
• Supports different Windows operating system versions: Windows 10, 8/8.1, 7, Vista etc

STEPS TO FIX Restoring SQL Server databases from .mdf files

Step 1: Stop the running MS SQL server. Perform the repair task on the copy of the corrupt database, click on the ‘OK’ button to continue.

Step 2: Click on the ‘Select database’ button and select the path of corrupt MDF file. You can also search your corrupt database file by using ‘Look in’ and ‘File Type’ button. click on the ‘Scan file‘ button to start the repairing process.

Step 3: The recoverable objects of database are listed in a tree view on the left side of the window. You can see the preview by clicking on the object.

Step 4: You can also search for a particular object by using ‘Find item’ option. Write the object name or a part of the object name in the given text box, check on ‘Match case‘ or ‘Match whole word‘ and then click find next button.

Step 5: Click on the ‘Start Repair’ icon. A dialog box will appear fill the SQL server instance name. To save the repaired file in the desired location click on the brows button and give the path, else the repaired file will be saved in the ‘Default SQL Location’. Click on the ‘OK’ button.

Compatibility and Support of SQL Database Repair Software

• Operating System – Windows 10, 8, 8.1, 7, 98, Vista, XP, Server 2003 and 2008
• MS SQL Server – 2000*64, 2000, Express, 2005, 2005*64, 2008 Express, 2008*64, 2008 R2, 2008, 2012, 2014,7.0 and other formats.

Simple Tips to Prevent SQL Database corruption

To make sure your database stays healthy you hove few “‘must do” task.

• Turn off scheduled shrink operations.
• Remove excessive transaction log file fragmentation
• Set auto-growth correctly.
• Turn on instant file initialization.
• Turn on AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS plus, in place to update statistics have a regular process.
• Have a regular process to run DBCC CHECKDB.
• Put regular process to detect and remove the index fragmentation.
• Turn on page checksums or torn-page detection on SQL Server 2000.
• Take regular full-database backup
• Use a powerful antivirus to stay away from computer virus infection.
• Ensure that you are not using a faulty hardware.
• If SQL Server encounters any problem then have a close look in the ERRORLOG file of your SQL Server.
• Never store the database in a shared network location if you find that your network connection or hardware is unreliable.
• Always exit database correctly.