SQLServerCentral Article

Reattaching Databases - Some Code AND a Contest!


Several months ago I published an article called Moving Databases to a New Server in which

I discussed a "trick" that allows you to quickly reattach ALL of your

databases. That trick works well as long as you're moving all of your files, but

what if you're only moving a few?

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
'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)
                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
                Debug.Print "Excluded : "; sDB
            End If
       End If
    Debug.Print "Folder specified does not exist"
End If
'always clean up
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


  • 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.