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
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 firstname.lastname@example.org.
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.