Attaching and Detaching Files

Sunday Mar 25th 2001 by Andy Warren
Share:

Recently I posted an article here on Swynk about how to move all of your data from one server to another. In that article I outlined a 'trick' you can use if the new server will have the same logical drive configuration as the old one. If the logical drive configuration is different or you only want to move a portion of your files, usually the easiest way is to detach them from the original server and then attach to the new one, which gives you the chance to change the physical locations of the files for each database.

Recently I posted an article here on Swynk about how to move all of your data from one server to another. In that article I outlined a "trick" you can use if the new server will have the same logical drive configuration as the old one. If the logical drive configuration is different or you only want to move a portion of your files, usually the easiest way is to detach them from the original server and then attach to the new one, which gives you the chance to change the physical locations of the files for each database. The downside to that is that if you have a lot of databases to move, it gets very tedious indeed!

If you haven't used them before, SQL gives you three stored procedures to use for this process; sp_detach_db, sp_attach_db, an sp_attach_single_file_db. Running sp_detach_db will remove the database from SQL, but leaves the files intact. You can then move them to a different location on the server or even to a different server, then use sp_attach_db to "reconnect" the database and you're back in business. Detaching the database is also useful when you want to archive a database but may need to use it again at some time in the future.

When you detach the database, you have the option to update the statistics before doing so. You can always update the stats after you have reattached it. You use sp_attach_single_file_db if you only have a data file, it will create the log file for you. If you have the log file, then you use sp_attach_db. SP_attach_db supports up to 16 files (a combination of data files and log files). If you have more than 16 files, you have to use create database with the for attach option.

When I read in BOL the 16 file limit, I was definitely curious - why would there be a limit on the number of files? It turns out that sp_attach_db has been coded to only accept 16 file names, and it basically just creates dynamic sql to execute a create database with the for attach option! I also looked at sp_attach_single_file_db, it just does a simple create database with attach, letting SQL create the log file in the default location. SP_detach_db isn't as helpful, it's just a wrapper around DBCC DetachDB - but you can guess that it probably just removes the row from sysdatabases plus other related master database tables.

Detaching and attaching databases in SQL 7 requires you to run the stored procedures, but in SQL 2000 you can right click to perform those operations - very very handy, since you have to provide the complete path and filename for each file. Unfortunately neither the stored procedures or Enterprise Manager allow you to specify filegroups when you attach. If you have file groups, you're stuck with manually creating the SQL to do the create database.

One potential problem with moving a database to another server is that your SQL logins will not work correctly. Neil Boyle has addressed that problem, see his article Fixing Broken Logins if you run into that problem.

So, in the worst case scenario, you're using filegroups and you're moving the database to a server with a different logical drive configuration (or you want to change how you're using the drives you have) - what's an easy way to do it? One way that wouldn't be too hard would be to script out the create database statement, then modify the file locations and add the 'for attach' to it. In SQL 2000 you could use the Copy Database Wizard (which also avoids the SQL login problem).

Other than that - code? As I've demonstrated in other articles, DMO gives you the ability to generate scripts programmatically with the same options you get when you script using Enterprise Manager. This would be faster than manually scripting each database, but would still require you to customize it afterwards (adding 'for attach' as a minimum, possibly changing file locations). I couldn't think of a way short of a copy of the Copy Database Wizard to handle the file locations, so maybe I could at least get the 'for attach' added.

So, there are two options. The first would be to use DMO to generate the create database script, then open it using the FileSystemObject and append the 'for attach'. The other would be to control the script process in code, walking through the collections to get the information needed. I opted for plan B because it gives me a chance to show you how the filegroups, dbfiles, and transactionlog collections work, but it is a LOT of code compared to plan A!

Take a look at the code!

This code shows to use to detach a database using SQL-DMO and also how to create a script to reattach the database. See comments in the code!

Sub DetachDB(ServerName As String, DBNamePattern As String, UpdateStatisticsFirst As Boolean)

'3/25/01 Andy Warren
' Shows how to detach a database using DMO and how to generate
'the reattachment script. DBNamePattern can the name
'of a database or a pattern using standard "VB" syntax (use * for
'wildcard, not %). Because sp_attach_db only supports up to 16 files
'and doesnt support filegroups, I've opted to always use the create
'database for attach method instead. If you're using SQL 2000 you
'should consider using the Copy Database Wizard. This code does not
'address SQL logins that may need to be moved
'
'Requires reference to MS SQL-DMO library plus the MS Scripting Runtime

Dim oServer As SQLDMO.SQLServer
Dim oDB As SQLDMO.Database
Dim vResult As String
Dim fso As FileSystemObject
Dim tsAttach As TextStream
Dim sFileName As String
Dim oDBFile As SQLDMO.DBFile
Dim oLogFile As SQLDMO.LogFile
Dim oGroup As SQLDMO.FileGroup
Dim iCount As Integer
Dim oFiles As Collection
Dim vFile As Variant
Dim sGrowthIncrement As String
Dim oUser As SQLDMO.User
Dim oLogin As SQLDMO.Login

On Error GoTo Handler

'simple err checking
If ServerName = "" Or DBNamePattern = "" Then
MsgBox "You MUST provide the server name and a database name/pattern", vbInformation + vbOKOnly, "Error"
Exit Sub
End If

'open connection to server
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With

'need this for file access
Set fso = New FileSystemObject

'loop through database collection to find ones that match our
'pattern
For Each oDB In oServer.Databases

'see if this db matches what we want to detach
If UCase(oDB.Name) Like UCase(DBNamePattern) Then

'default to the root, but could the the app.path
'property or hardcod differently, we're creating
'one file per db
sFileName = "C:\AttachDB_" & oDB.Name & ".sql"

'open and overwrite any previous file
Set tsAttach = fso.CreateTextFile(sFileName, True, False)

'put some header comments in
tsAttach.Write "--" & Format$(Now, "mmm dd, yyyy")
tsAttach.WriteLine "--Script to attach/create database " & oDB.Name
tsAttach.WriteBlankLines 1

'switch to master
tsAttach.WriteLine "Use Master"
tsAttach.WriteLine "GO"
tsAttach.WriteBlankLines 1

'build create db, always have to have a Primary file group
tsAttach.WriteLine "Create Database " & oDB.Name & " on PRIMARY "

'process the data files first, could be multiple filegroups
For Each oGroup In oDB.FileGroups
'reset on each pass
iCount = 0
For Each oDBFile In oGroup.DBFiles
'have to count each file so we know when we are at the end
iCount = iCount + 1

'the only tricky part here is deciding whether we need a comma
'at the end of each pass
With tsAttach
.WriteLine "("
.WriteLine "Name='" & oDBFile.Name & "',"
.WriteLine "FileName='" & RTrim$(oDBFile.PhysicalName) & "',"
.WriteLine "Size=" & oDBFile.Size & ","
'DMO returns a -1 if set to unlimited growth
If oDBFile.MaximumSize <> -1 Then
.WriteLine "(MaxSize=" & oDBFile.MaximumSize & ","
End If
If oDBFile.FileGrowthType = SQLDMOGrowth_MB Then
sGrowthIncrement = "MB"
Else
sGrowthIncrement = "%"
End If
.WriteLine "FileGrowth=" & oDBFile.FileGrowth & sGrowthIncrement
If iCount = oGroup.DBFiles.Count Then
.WriteLine ")"
Else
.WriteLine "),"
End If
End With
Next

'its possible to have a filegroup with no files, and we script will fail
'if we try to add another filegroup called Primary
If oGroup.DBFiles.Count > 0 Then
If oGroup.Name <> "PRIMARY" Then
tsAttach.WriteLine "FileGroup " & oGroup.Name
End If
End If
Next

'separator
tsAttach.WriteLine "Log on"

'reset counter
iCount = 0
For Each oLogFile In oDB.TransactionLog.LogFiles
iCount = iCount + 1
With tsAttach
.WriteLine "("
.WriteLine "Name='" & oLogFile.Name & "',"
.WriteLine "FileName='" & RTrim$(oLogFile.PhysicalName) & "',"
.WriteLine "Size=" & oLogFile.Size & ","
If oLogFile.MaximumSize <> -1 Then
.WriteLine "MaxSize=" & oLogFile.MaximumSize & ","
End If
If oLogFile.FileGrowthType = SQLDMOGrowth_MB Then
sGrowthIncrement = "MB"
Else
sGrowthIncrement = "%"
End If
.WriteLine "FileGrowth=" & oLogFile.FileGrowth & sGrowthIncrement
If iCount < oDB.TransactionLog.LogFiles.Count Then
.WriteLine "),"
Else
.WriteLine ")"
End If
End With
Next

'have to add this!
tsAttach.WriteLine "For Attach"

'run each as a separate batch
tsAttach.WriteLine "GO"

'close the object
Set tsAttach = Nothing

'detach it - this method returns a string, but we dont need it
vResult = oServer.DetachDB(oDB.Name, True)
End If
Next

'standard clean up
On Error Resume Next
Set fso = Nothing
oServer.DisConnect
Set oServer = Nothing

Exit Sub

Handler:
If MsgBox(Err.Description & ". Would you like to continue?", vbInformation + vbYesNo, "InstallDB") = vbYes Then
Resume Next
End If

End Sub

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved