for a hundred good reasons .... and I promise that at least a few are good ... I find myself with almost 200 SQL server databases (mdf,ldf files) generated by different versions of sql server, most 2005.

I need to verify the integrity of each and follow up on the ones that fail.

after all that .... My question is:

Is it possible to attach - verify - detach each database using a batch file or a simple script?

I've used sqlcmd for similar jobs, but with this volume I thought I'd see if there was a simpler way.

  • Are they not currently attached to some instance?
    – gbn
    Commented Jan 21, 2012 at 12:06
  • 1
    I don't think there is any "easy button" app out there that will do it. I can think of ways to write the script using PowerShell or T-SQL, and there aren't a whole lot of wrinkles in it, so I'd suggest you start writing the script and post if you have any issues. Commented Jan 21, 2012 at 17:24

1 Answer 1


This one was quite tricky. I didn't figure out how to access DBCC messages, but am working on it. Anyway, this Powershell script attaches databases, executes DBCC CHECKDB and detaches dbs.

# Get database files from where ever they are
$databases = gci "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data2\*.mdf"
$sqlCmds = @()
# Parse db names from file names somehow and generate attach/check/detach statements
foreach($db in $databases) {
  $dbName = [regex]::Replace($db.Name, "(?i)\.mdf", "")
  $lgName = [regex]::Replace($db.FullName, "(?i)\.mdf", ".ldf")
  $sqlCmds += $("CREATE DATABASE {0} ON (FILENAME = '{1}') LOG ON (FILENAME = '{2}') FOR ATTACH;"  -f $dbName, $db.FullName, $lgName)
  $sqlCmds += $("DBCC CHECKDB ({0});" -f $dbName)
  $sqlCmds += $("EXEC sp_detach_db @dbname='{0}';" -f $dbName)

$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.ConnectionString = "Server=SERVER\INSTANCE;Database=master;Integrated Security=True"
$s = $sqlCmds[0]

# Try and execute the statements   
try {
    foreach($s in $sqlCmds) {
      $ret = $conn.ExecuteNonQuery($s)
} catch [system.exception] {

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.