Sql-server – Do I need to back up the log file drive when using VSS snapshots in simple mode

Sorry to disappoint you, but VSS Backups of SQL Server are fully supported by Microsoft through the SQL Writer Service (http://technet.microsoft.com/en-us/library/ms175536.aspx).

When the third party backup application wants to take a backup, SQL Writer tells SQL Server to "Freeze IO" on the database. After the IO is frozen, then a snapshot is taken of the mdf and ldf files. After the snapshot completes, IO is unfrozen. This does prevent all write activity to the database while the snapshot is being generated, and could take 60 seconds or longer. The third party backup app is then copies the snapshots of ldf and mdf files. If you test the third party backup application and you see messages in the error log about Freezing and Unfreezing of IO for your databases, then it's using the SQL Writer interface, and the backups are good.

Full Description here: http://technet.microsoft.com/library/Cc966520

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
    }