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
'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

Reattaching Databases - Some Code AND a Contest!

Rate

Share

Share

Rate