Conversion Of MDF File to .SQL File

Overview

In SQL Server, every database contains .mdf files. MDF file is a primary database file which is also known as Master Database File. MDF Files contains all the information of SQL Server Database objects like tables,views,triggers etc. Whereas, the .sql Script is a set of command which contains one or more SQL statements. These Scripts can be used for further modification or regeneration of database objects.

Why to Convert MDF File TO .sql Script

There are various reasons for converting mdf file into .sql script which are as follows:

  1. By converting .mdf files into .sql script, one can use these files to different servers.
  2. Sometimes users attach MDF files to the database by some untrusted sources, which may result in error as it could contain malicious information. So at this time we can convert mdf files into .sql script and execute these scripts on SQL Server for creating that database .

How to Convert MDF Files to .sql Script

You can try different methods for converting MDF Files into .sql as per your requirement. They are as follows:

  • Manual method
  • Alternate method

Manual Method to Convert MDF File into SQL Script

  1. Launch Microsoft SQL Server Management Studio and connect to the SQL Server.
  2. Click on the Database option and select the database for which you want to generate .sql script.
  3. Now right click to your Database, you will get the option ‘Tasks’ followed by ‘Generate Scripts’.Click on that.
  4. After clicking on ‘Generate Scripts’, you will get the ‘Introduction’ screen in which you have given steps to generate the script. Click on ‘Next’ to proceed.
  5. After that you will get the ‘Choose Objects’ screen in which two options have been mentioned. Choose ‘Script entire database and all database objects’ option to select the whole database objects, and then click Next.
  6. SQL Server provides you three different options through which data can be exported i.e. ‘Data only’, ‘Schema only’ and ‘Data and Schema’. Choose the type of data according to your need.For this you have to click on 'Advanced' button.
  7. After clicking on it, you will get the ‘Advance scripting options’ screen, in which you can select the type of data. Click ‘Type of data in script’ option and choose its type.
  8. After choosing the type, you will get the screen of ‘Review your selection’, in which you will get to know about your server name, database, its selected objects and the target location of your database.
  9. After that you will get the report of your database objects which you have generated using ‘Generate Script’ option.

Alternate Method to Convert MDF Files into SQL Script

You can use alternate option for converting MDF Files to .sql script by using professional tool name SQL Recovery Software.

For this, here is the easy steps to convert mdf file to sql :

  1. Launch SysTools SQL Recovery Software and Click on ‘Open’ button to upload mdf files.
  2. After uploading mdf files, Go to 'Export' button to export the files.
  3. You will get Export options after clicking on ‘Export’ button, Click on ‘SQL Server Compatible SQL Scripts’ to export in sql script. It saves your script to the local system.
  4. Choose the type of database object accordingly.
  5. Click ‘With Schema’ to create only schema for tables,views,triggers etc Or ‘With Schema and Data’ to export schema and data for table.Choose 'With Schema and Data’
  6. Choose the location to save the .sql file.
  7. A pop up with ‘Export Completed’ confirming the successful export of the database.

Conclusion

In this blog, I have discussed about the mdf files and need to convert mdf files to .sql script. The advantage of converting into sql script is that we can use mdf files to different servers. The blog also discusses the possible solutions on how to convert MDF file in .sql script manually or by using third party tool in pictorial representation.

Previous
Next Post »

EmoticonEmoticon