Sql-server – simply copy mdf and log files into the data folder to add a new database

sql serverssms

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.

Best Answer

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
Related Question