SQL Server – How to Attach a Corrupted Database

Right.. I figured it out.. some clown here (who won't own up to it) renamed the db so that its name in SSMS is different to the mdf and ldf file names.. and not just a bit different... completely different. LOL. Thanks all for your helpful suggestions anyway.

There's no way to find those files with a detached database .mdf. As Aaron suggests, you can create the database with ATTACH_REBUILD_LOG. Another option is if you have an "old" backup file of the database, you can use RESTORE FILELISTONLY to interrogate the backup file for the state of the database files at the time of that backup. This will give you a starting point to track down your files.

Edit Because I like Powershell, here's a script that will read through all the full backup files in a directory and build an attachdbs.sql in your Documents folder:

param([parameter(Mandatory=$true)][string] $dir,
        [parameter(Mandatory=$true)][string] $server)


#load assemblies
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server


#get backup files
$files = gci $dir | where {$_.name -like "*.bak"}

$output=([Environment]::GetFolderPath("MyDocuments")) + "\attachdbs.sql"

"/*****************************************" > $output
"Attach script based off of backup files" >> $output
"*****************************************/" >> $output
foreach($file in $files){
    $rs=new-object("Microsoft.SqlServer.Management.Smo.Restore")
    $rs.Devices.AddDevice($file.FullName, "File")
    $hd=$rs.ReadBackupHeader($smosrv)
    $dbname=$hd.Rows[0].DatabaseName

    $dbfiles=$rs.ReadFileList($smosrv)

    "CREATE DATABASE $dbname ON" >> $output
    $filewrite=@()
    foreach($dbfile in $dbfiles){
        $filewrite+="(FILENAME='"+$dbfile.PhysicalName+"')"
    }

    $filewrite -join ",`n" >> $output

    "FOR ATTACH; `n--------------------------" >> $output
    }