0

I have a copy of the mdf and log files of a database. The database is a MSSQL 2012 database. To use it, I did the following steps (a bit by chance actually, because I was trying to copy a mdf and log file out of the data directory first, and it wouldn't let me while the server was using them):

  1. I stopped the MSSQL server process in Windows
  2. Copied them into the DATA directory.
  3. Then I started the MSSQL servcer process again.

In SQL server management studio, the new database appeared. I thought I might have needed to attach it.

Does MSSQL server automatically attach all mdf's in the data directory? I'm worried it is not working as it should, even though it looks like it.

  • 1
    because I was trying to copy a mdf and log file out of the data directory first -> why? These are not spreadsheet files and this is not how you move them around. See blogs.sentryone.com/aaronbertrand/bad-habits-file-backups – Aaron Bertrand Mar 5 at 1:08
  • @AaronBertrand Because I received a mdf and log file from a third party that has a back up routine on our server. I wanted to take a copy of the existing mdf and log file before installing them. These were back ups of an existing base, could that be why they were picked up automatically? – ptf Mar 5 at 8:10
  • Again, these aren't backups, even if a third party tells you they are. – Aaron Bertrand Mar 5 at 13:20
  • @AaronBertrand Alright, thanks. I'll keep that in mind. They are in use by a production site now, and it seems to be working fine. Do you know if it will cause any problems? – ptf Mar 5 at 20:15
  • Yes, it certainly will. SQL Server has native backups for a reason - if we could always just rely on mdf/ldf files as backups, life would be so simple... a lot of things seem to be working fine, until they aren't. When all you have to rely on is some OS-level file-based copy of a file, and it doesn't work, how will you get back online? – Aaron Bertrand Mar 5 at 20:32
5

Does MSSQL server automatically attach all mdf's in the data directory?

No it does not attach a new mdf and ldf automatically.

Instead of fiddling around with stopping sql service and copying around mdf and ldf files, I suggest you take a look at backup and restore commands.

backup database foo 
to disk = 'd:\backup\foo_full.bak' 
with init, stats =10, compression

-- now restore the database with move and recovery

restore database foo 
from disk = 'd:\backup\foo_full.bak' -- backup location
with recovery, stats =10,
move 'logicalName' to 'M:\data\foo.mdf', -- mdf location
move 'logicalName_log' to 'L:\log\foo_log.ldf' -- ldf location
  • I can add that this was a backup copy of an existing mdf, just with a different name, that we received from a third party. Could that be why it was automatically picked up? Thanks for the commands. – ptf Mar 5 at 8:07
  • No, SQL server will not pick it automatically, You need to attach it manually. – Mukesh Arora Mar 5 at 9:55
  • @MukeshArora That is strage, because it just showed up automatically, and it seems to be working. – ptf Mar 5 at 20:14
  • @PeterTollnesFlem I can assure you that plopping an MDF/LDF file in a data folder does not make it magically appear in SQL Server. – Aaron Bertrand Mar 5 at 20:33
  • @AaronBertrand That's strange then. I started the MSSQL service, and there it was. Maybe it was because it was from the same SQL server previously. – ptf Mar 6 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.