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.

7
  • 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 Mar 5, 2019 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, 2019 at 8:10
  • Again, these aren't backups, even if a third party tells you they are. Mar 5, 2019 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, 2019 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? Mar 5, 2019 at 20:32

1 Answer 1

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
6
  • 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, 2019 at 8:07
  • No, SQL server will not pick it automatically, You need to attach it manually. Mar 5, 2019 at 9:55
  • @MukeshArora That is strage, because it just showed up automatically, and it seems to be working.
    – ptf
    Mar 5, 2019 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. Mar 5, 2019 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, 2019 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.