Take the 2-minute tour ×

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.

share|improve this question

migrated from stackoverflow.com Jan 21 '12 at 14:59

This question came from our site for professional and enthusiast programmers.

Are they not currently attached to some instance? –  gbn Jan 21 '12 at 12:06
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. –  Aaron Bertrand Jan 21 '12 at 17:24

1 Answer 1

up vote 2 down vote accepted

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] {
share|improve this answer
Thanks! .... I see what i was missing. –  Andrew Neelands Jan 29 '12 at 18:02

Your Answer


