How to Restore Sql Server Databases From .Mdf Files

Microsoft’s SQL Server Database is one of the famous database, this database is fast, flexible and secure. It allows users to disconnect from the main server program and create secure backup copies or transmit data to branch offices. After detaching database connection, you should restore from its .MDF file with the help of “attachment” method. To do this task you can apply any one from two ways, first is via Management Studio program’s graphical interface and second is using typed Transact-SQL text commands.

 

START SQL SERVER MANAGEMENT STUDIO

Step 1: Run the “Microsoft SQL Server” (click on the “Start” button à “All Programs” à“Microsoft SQL Server”). Open the record of SQL Server programs.

Step 2: Click on the “SQL Server Management Studio”.

Step 3: Choose 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” option available within the Management Studio’s toolbar.

Step 2: Click on the text area and type Create Database statement using Transact-SQL code given below:

CREATE DATABASE MyDatabase ON (FILENAME = ‘c:\data files\my_data.mdf’), (FILENAME = ‘ c:\data files\my_data.ldf’) FOR ATTACH;

Step 3: Press “Execute” button in Transact-SQL toolbar, present under Management Studio’s main toolbar. Through this way SQL Server Management Studio recovers database.

 

Different types of CORRUPTION ISSUES in SQL DATABASE FILES

There are lots of issues that can affect .MDF and .NDF file and make them corrupt. Some causes are given below:

• Malfunctioning in the system’s hardware is one of the common issues that can corrupt database file.
• Unexpected power failure while doing any input/output work
• Sometime due to replication in the database can also lead to data loss.
• When you rename the primary file group then it will not change but can affect the database.
• Corruption in the file header
• Corruption of storage media where MDF files are stored
• While storing SQL database in compressed folders
• Modifications in SQL Server account

MANUAL PROCESS TO REPAIR RESTORING SQL SERVER DATABASES FROM .MDF FILES

You can repair a corrupt or damaged SQL Server Database with the help of below given manual method. There are lots of options available to repair Server databases from .mdf files.

Note: Before Applying this manual methods make sure that no Webtrends services are start.

USING MICROSOFT SQL SERVER MANAGEMENT STUDIO EXPRESS

Step 1: First you have to download MS SQL Server Management Studio Express, according to the version of your database.

o    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

o    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

Step 2: Install the MS SQL Server Management Studio. To open it, Start -> Programs -> Microsoft SQL Server 2005/2008 -> SQL Server Management Studio Express.

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

Step 4:  Expand the Databases and right-click on wt_sched and select Properties -> Options.

Step 5: Modify Restrict Access to SINGLE_USER.

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

Step 7: Select New Query and paste the given commands in query pane.

dbcc checkdb(‘wtMaster’,REPAIR_REBUILD)

dbcc checkdb(‘wt_sched’,REPAIR_REBUILD)

Step 8: Again choose the Execute button to repair SQL database.

Step 9: At last, after performing the steps 4 & 5 change the databases back from SINGLE_USER to MULTI_USER.

Read more about this issue: http://sqlmdf.filerepairtool.net/blog/restoring-sql-server-databases-mdf-files