SQL Server MDF file

Question

Hello,

I am working on a website that has access data from a database (sql server). It will also be adding, updating deleting records in the database. It seems like there is an MDF file that gets created containing the database schema and all the records I guess? Once development is complete and I want to move this database to a real server then all I need to do is move this MDF file to the real server and that is it? is it that simple? or not really?

Also, I hope the MDF file is not a read-only file and can be updated/modified or is it?

Answer 1

Yes, the MDF data file (and the associated *.LDF transaction log file) contains all the database objects that make up your system.

And yes, you can definitely detach a database (an .MDF/.LDF file pair) from your (dev) SQL Server, copy it onto another "real" Production SQL Server, and re-attach it there, without loss of data or information.

Yes, the MDF file is modifyable - through the SQL Server commands and methods. You should never tamper with the file yourself, directly (flipping bits). Use SQL Server and its official interfaces (T-SQL, ADO.NET etc.) to work with your data.

Answer 2

You will need to take .MDF file as well as .LDF file which will have your logs. You have to take both mdf and ldf files and attach on the real server.

Answer 3

Yes you can do that. Right click the database and click "detach". Make sure you click the drop connections box and click ok (if you don't do this some errors could occur). You can now move the mdf and ldf files.

However, there is a better way imho. Instead, create a full backup of the database, copy the backup file, and restore from the backup on the new server. Now you have two copies of the database, a development version and a production version.