Thank this author by sharing:
By Andy Warren,
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:
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
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 & "\"
'connect using a trusted connection
Set mSQLServer = CreateObject("SQLDMO.SQLServer")
.LoginSecure = True
'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))
'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
Debug.Print "Folder specified does not exist"
'always clean up
Set mSQLServer = Nothing
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
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 email@example.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.
'string' = 'string___' ???
Powerful function to find strings containing or excluding classes of ASCII characters.
How can I exclude results containing certain words?
Attach a suspect database
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.