Because more than one person was meddling with the database, we have lost our transaction log files and consequently lost the database. Luckily, we have the MDF file still perfectly intact, and we're not worried about the lost transactions, since they were only 'delete' transactions.
I did some research, and found that the sp_attach_single_file_db command is pretty much meant for this, and the sql server was shutdown before the file was removed. I used this command:
EXEC sp_attach_single_file_db @dbname = 'RecoveredDatabase',
@PhysName = 'E:\SQL Data\AerationBasinsTrend_data.mdf'
Where 'RecoveredDatabase' is my new database, and 'E:\SQL DATA\AerationBasinsTrend_Data.mdf' is the phyiscal .mdf file. I get an error when I run this. The server reports this:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'RecoveredDatabase'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\SQL Data\AerationBasinsTrend_Log.LDF' may be incorrect.
Device activation error. The physical file name 'C:\SQL Backup\TransLog' may be incorrect.
As far as I know, this command should create a new ldf file if the old one is not present. What am I missing?
Best Answer
You can give this a try:
Source