Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 25, 2001

Attaching and Detaching Files - Page 2

By Andy Warren

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM