So what if you lose the disk that holds your transaction logs – and all database backup files. Of course, there is no DR plan available (if you put transaction log and backup files on the same disk, DR is clearly no priority).
There are 3 options here
Plan A: If your database was stopped in a clean way (eg detach), you can run following:
CREATE DATABASE [TestDB] ON
(FILENAME = N'D:\MSSQL\SQL2008\Data\TestDB - Copy.mdf')
But what are the chances of this when you disk suddenly decides not to do anything anymore?
Anyway, you’ll soon find out when running previous code, you will get an error like this:
File activation failure. The physical file name "D:\MSSQL\SQL2008\Data\TestDB_log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Test'. CREATE DATABASE is aborted.
So you have plan B: Restore from backups.
Oops, the backup files were on the same disks. So no plan B.
And so there is a plan C: Repair your DB with data loss. There will be data lost, but that is still better than not having any DB at all.
Step 1: As your DB will be in the Recovery Pending state, you’ll have to bring it into SINGLE_User mode first:
ALTER DATABASE TestDB SET EMERGENCY
ALTER DATABASE TestDB SET SINGLE_USER
Step 2: run DBCC CHECKDB, which will automagically generate a transaction log that is missing.
DBCC CHECKDB(TestDB, REPAIR_ALLOW_DATA_LOSS)
Step 3: run CHECKDB again to check for inconsistency.
DBCC CHECKDB(TestDB) WITH NO_INFOMSGS
Step 4: put the DB back into MULTI_USER mode
ALTER DATABASE TestDB SET MULTI_USER