Database.EnumFiles
-
I'm currently using DMO objects for database backup and restore in a VB interface.
I was interested by the physical location of the DB files, and I first tried to use the EnumFiles method of the DB object:
Set oQRTemp = oDB.EnumFiles
Dim i As Integer
For i = 1 To oQRTemp.Rows
Set oItem = ListView1.ListItems.Add(, , oQRTemp.GetColumnLong(i, 1))
oItem.SubItems(1) = oQRTemp.GetColumnString(i, 2)
oItem.SubItems(2) = oQRTemp.GetColumnLong(i, 3)
oItem.SubItems(3) = oQRTemp.GetColumnLong(i, 4)
oItem.SubItems(4) = oQRTemp.GetColumnLong(i, 5)
oItem.SubItems(5) = oQRTemp.GetColumnString(i, 6)
oItem.SubItems(6) = oQRTemp.GetColumnLong(i, 7)
oItem.SubItems(7) = oQRTemp.GetColumnLong(i, 8)
oItem.SubItems(8) = oQRTemp.GetColumnLong(i, 9)
Next i
The SQL-DMO Reference Help says that "The EnumFiles method returns a QueryResults object that enumerates the operating system files used to implement Microsoft® SQL Server™ 2000 database storage." and that the second column of the QueryResults object should contain the name of the operating system files. Well, what I get are numbers: 1 and 2...
So for now I used 2 other different properties to get what I want:
strCurrMDFPath = oDB.FileGroups(1).DBFiles(1).PhysicalName
strCurrLDFPath = oDB.TransactionLog.LogFiles(1).PhysicalName
But I'd really like to know if I'm doing something wrong when I use the EnumFiles method of the DB object... or if I just don't understand correctly what this method returns.
So if anybody out there knows a bit more than I do about it...
Thanks
-
hi,
I do not know if I correctly understand your needs, but, personally , wanting to restore a backed up database I do not scan the database's files but the restore object content... then I can decide if I have to specify the WITH MOVE option of the RESTORE syntax...
assuming you want to inspect the actual content of the pubs database you backed up to file = c:\pubsC.Bak, you can get the info about the original database's file physical location as following..
Dim iCol As Integer, iRow As Integer
Dim oSvr As SQLDMO.SQLServer
Set oSvr = New SQLDMO.SQLServer
With oSvr
.ApplicationName = App.EXEName
.AutoReConnect = True
.LoginSecure = True
.LoginTimeout = 1
.Connect "(Local)"
End With
Dim oRest As SQLDMO.Restore
Set oRest = New SQLDMO.Restore
oRest.Files = "[c:\pubsC.Bak]"
Dim oQry As SQLDMO.QueryResults
On Local Error Resume Next
Set oQry = oRest.ReadFileList(oSvr)
On Local Error GoTo 0
Set oRest = Nothing
If Not oQry Is Nothing Then
If oQry.ResultSets <> 0 Then
oQry.CurrentResultSet = 1
For iCol = 1 To oQry.Columns
Debug.Print oQry.ColumnName(iCol),
Next
Debug.Print
For iRow = 1 To oQry.Rows
For iCol = 1 To oQry.Columns
Debug.Print oQry.GetColumnString(iRow, iCol);
Next
Debug.Print
Next
End If
End If
Set oQry = Nothing
Set oSvr = Nothing
hth
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface)
-
hi,
thanks for your answer and sorry, I was not precise enough when I said I was interested in the "physical location of the DB files". I meant the current physical location of the DB files, not the physical location of the DB files when I backed it up (which I'm intersted in as well, but for this I had found and I use the ReadFileList method of a Restore object that you mentionned).
So if I have a DB on my SQLserver, and I want to know what is the physical location of the files (data and log), what is the best way to do it with SQLDMO?
-
hi,
Dim i As Integer, y As Integer
Dim oSvr As SQLDMO.SQLServer
Set oSvr = New SQLDMO.SQLServer
With oSvr
.ApplicationName = App.EXEName
.AutoReConnect = True
.LoginSecure = True
.LoginTimeout = 1
.Connect "(Local)"
End With
Dim oDB As SQLDMO.Database
Set oDB = oSvr.Databases("Pubs")
For i = 1 To oDB.FileGroups.Count
Debug.Print "Filegroup: " & oDB.FileGroups(i).Name
For y = 1 To oDB.FileGroups(i).DBFiles.Count
Debug.Print "File Logical Name " & oDB.FileGroups(i).DBFiles(y).Name
Debug.Print "File PhysicalName Name " & oDB.FileGroups(i).DBFiles(y).PhysicalName
Next
Next
Debug.Print "Transaction logs"
For i = 1 To oDB.TransactionLog.LogFiles.Count
Debug.Print "File Logical Name " & oDB.TransactionLog.LogFiles(i).Name
Debug.Print "File PhysicalName Name " & oDB.TransactionLog.LogFiles(i).PhysicalName
Next
Set oDB = Nothing
Set oSvr = Nothing
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface)
-
Yep, that's what I'm using now. So I guess there's not better/faster/easier way.
Thanks anyway.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply