Reattaching Databases - Some Code AND a Contest!

In SQL 7 you have no choice - you have to use stored procedures, you can't reattach files through Enterprise Manager. If you're only doing a two or three it's not bad. Many more than that and you'll be wishing for a better way! SQL 2000 is a little better, you can reattach from Enterprise Manager AND it automatically identifies the log files. This eliminates the hassle of finding the correct file name and avoids typo's at the same type. It's still slow though if you have many to do.

At this point, if you're a power user, you can have options. One is get your database file names into a table, then cursor through and execute the attach for each one. The other is to use DMO. Both do basically the same thing. If you're comfortable in TSQL, you may prefer the first. If VB or VBScript is your strength, the second may be a better choice.

I'm the DMO guy here at SQLServerCentral, so the solution is naturally VB & DMO:-) The code below won't handle all cases, but it's a good start. I wrote this in VB6, if you want to use it as VBScript you'll have to remove all of the "as datatype" portions from the Dim statements. Let's take a look at the parameters you need to provide:

• ServerName. Nothing big here, it's not case sensitive, no error checking done. If it's spelled wrong the connection will fail.
• MappedMDFLocation. This can trip you up. Let's say that for this example, you keep your data in D:\Data on  your server. You're logged in at your workstation and you have mapped that drive as X in Explorer. You HAVE to specify the MappedMDFLocation as X:\Data, not D:\Data. Your code will be running locally, so for the file system object to find your files it must be from the perspective of where the code runs.
• ServerMDFLocation. This is always the location on the server. Drawing from our MappedMDFLocation notes, this would be "D:\Data".
• ExclusionList. You're not going to want to attach master, msdb, temp, or model, so I've added code to always handle those. If there are 50 databases to attach and a few you want to exclude,  you list their names here separated by a "/" - take a look at the code to see how it works.
• FileMask. I've set this up as an optional parameter that defaults to *.MDF - all of the db's in the folder. This gives you the ability to create a filter to identify which ones to attach. If you're trying to attach A through M, this is probably faster than explicitly excluding all the db's with names N through Z.

And here is the code:

Sub ReattachMultipleDB(ServerName As String, MappedMDFLocation As String, ServerMDFLocation, ExclusionList As String, Optional FileMask as string = "*.MDF")

'8/18/01 Andy Warren
'  Reattaches all of the mdf files in a folder to SQL. MappedMDFLocation is the folder name
'as you see it in Explorer on the machine you're working on. If you're on the server, its the
'same as ServerMDFLocation. If you're working from a workstation, MappedMDFLocation WILL be
'different! If you want to specify dbnames to exclude, pass them all as one string delimited
'with a slash - see note about the sExclude variable below. This version doesnt handle
'multiple data files. Set references to MS SQLDMO and MS ScriptingRuntime to use in VB.

Dim mSQLServer As SQLDMO.SQLServer
Dim mDatabase As SQLDMO.Database
Dim oFile As Scripting.File
Dim fso As Scripting.FileSystemObject
Dim oFolder As Scripting.Folder
Dim iBreak As Integer
Dim sDB As String
Dim sExclude As String

'these are excluded by default - using the slash as a delimiter allows us to use
'instr later on to do a quick simple test to see if any particular db name is in
'the list. This is also easier than requiring the user to pass an array into the
'sub
sExclude = "MASTER/MSDB/MODEL/TEMPDB/"

'add any passed by the user
sExclude = sExclude & UCase$(ExclusionList)

'make sure server location has trailing backslash
If Right$(ServerMDFLocation, 1) > "\" Then
 ServerMDFLocation = ServerMDFLocation & "\"
End If

'connect using a trusted connection
Set mSQLServer = CreateObject("SQLDMO.SQLServer")
With mSQLServer
  .LoginSecure = True
  .Connect ServerName
End With

'use the fso to get the list of files to process
Set fso = CreateObject("Scripting.FileSystemObject")

'make sure folder exists
If fso.FolderExists(MappedMDFLocation) Then
    Set oFolder = fso.GetFolder(MappedMDFLocation)
    For Each oFile In oFolder.Files
       If UCase$(oFile.Name) Like UCase$(FileMask) Then
        'most files have _DATA or _LOG, strip that out of what will be the
        'db name along with the file extension

        iBreak = InStr(oFile.Name, "_")
            If iBreak > 0 Then
                sDB = Left$(UCase$(oFile.Name), iBreak - 1)
            Else
                sDB = UCase$(Left$(oFile.Name, Len(oFile.Name) - 4))
            End If

            'if the dbname is not one of the ones to be excluded, reattach
            If InStr(sExclude, sDB) = 0 Then
                mSQLServer.AttachDB sDB, ServerMDFLocation & oFile.Name
                Debug.Print "Reattached: "; sDB
            Else
                Debug.Print "Excluded : "; sDB
            End If
       End If

    Next
Else
    Debug.Print "Folder specified does not exist"
End If

'always clean up
mSQLServer.Disconnect
Set mSQLServer = Nothing

End Sub

Is this something you'll do often? No. If you ever need to do it, having a script to work from will save you a LOT of time. Set up a quick test environment, detach Pubs and Northwind, give it a try. Not only will you become more familiar with attaching and detaching, you'll have the chance to see both DMO and the FileScriptingObject being used - which may get you thinking about other problems you can solve!

But now we get to the good part. We need a good TSQL script to contrast with the VB solution I've presented. Readers, ready for a challenge? Here are the rules:

• We're looking for the purest TSQL solution you can create
• Create a stored procedure that has the same parameters as I used in my code and has the same behavior (support filemask, exclusion, etc)
• You can use anything except DMO

And what's in it for you? Fame! Well, fame plus an incredibly off topic book, JMS Messaging by Wrox! Submit your solutions to me at awarren@sqlservercentral.com. Deadline is 14 days after the article is published, we'll present the results and announce the winner a week later. My fellow columnists will serve as the judges for this contest.