Restore a SQL SERVER database from a mdf file

Question

I need to restore a database from an MDF file. I took the mdf from another machine to be restored on a main machine.

While performing the attach, linking the MDF file, an error occurs saying that the file cannot be located, however the address of the file displayed is the address of the machine that recovered the MDF file and not that of the main machine.

I tried to perform the attach via command by specifying the addresses via command:

sp_attach_single_file_db

As I expound below:

exec sp_attach_single_file_db @dbname = 'base_recuperada',
@physname = 'C:\sqldata\dados.mdf'

While executing the command occurred to me the following error:

Mensagem 5120, Nível 16, Estado 5, Linha 15
Não é possível abrir o arquivo físico "I:\DADOS_SERVER\base.mdf". Erro do sistema operacional 2: "2(O sistema não pode encontrar o arquivo especificado.)".
Falha de ativação do arquivo. O nome do arquivo físico "I:\DADOS_SERVER\base.ldf" pode estar incorreto.
Mensagem 1813, Nível 16, Estado 2, Linha 15
Não foi possível abrir o novo banco de dados 'base_recuperada'. CREATE DATABASE foi anulado.

If you look at the location address in the above message you will see that the address you are referencing is still wrong and I cannot set the correct location.

Even after this situation I tried a third alternative, I created a new database and tried to link the mdf file to which I am restoring and the same wrong location problem occurred.

I can’t recover a bkp from the bank and I can’t untie the bank file from the other machine. Only with the mdf I can restore on the main machine.

What suggest procedure or what would be correct?

Answer 1

This error happens because ldf file is missing.
An alternative is to create a new database and insert the mdf into it. It can be done via command:

CREATE DATABASE dados ON (FILENAME = N'C:\sqldata\dados.mdf')
FOR ATTACH_REBUILD_LOG

The ATTACH_REBUILD_LOG will recreate the missing log