If you don't browse our discussion area on a regular basis you're missing out
on a great learning opportunity. Even when you're familiar with
something...sometimes there is still something to learn! We had a recent post
asking two questions - how t get the logical file name from a backup and how to
restore a backup using norecovery - using DMO in both cases. I rarely need the
logical file name but I was pretty sure I knew where to find it, but the
norecovery....had not had a reason to use it (mostly because when I do an
automated restore its a full restore for a testing db).
Hopefully the first part is familiar to you (see Introduction
to SQL-DMO, lots of other DMO material here on the site as well), we're
connecting to the server and doing a full backup of Pubs.
Dim oserver As SQLDMO.SQLServer
Dim oBackup As SQLDMO.Backup
Dim orestore As SQLDMO.Restore
Dim oResults As SQLDMO.QueryResults
Dim J As Integer
Dim K As Integer
'create standard server object first
Set oserver = New SQLDMO.SQLServer
With oserver
.LoginSecure = True
.Connect "."
End With
'do a quick backup
Set oBackup = New SQLDMO.Backup
With oBackup
.Database = "pubs"
.Action = SQLDMOBackup_Database
.Files = "C:\test.bak"
.SQLBackup oserver
End With
Set oBackup = Nothing
|
Getting the file list is reasonably straight forward. The restore object has
a ReadFileList method that returns a queryresults object. I've added code here
to dump out everything returned.
'get filelist only
Set orestore = New SQLDMO.Restore
With orestore
.Action = SQLDMORestore_Database
.Files = "C:\test.bak"
Set oResults = .ReadFileList(oserver)
End With
Set orestore = Nothing
'view the filelist
If Not oResults Is Nothing Then
'print out all columns in each row
For K = 1 To oResults.Rows
For J = 1 To oResults.Columns
Debug.Print oResults.ColumnName(J) & Space$(15 -
Len(oResults.ColumnName(J))) & ": " & oResults.GetColumnString(K, J)
Next
Next
End If
Set oResults = Nothing |
This is what I get when I run it. Makes sense, there are only two file for
the standard Pubs database. If we only want the file name we can just grab the
value from column number two (or to be safer run through the columns to find the
one that matches 'PhysicalName' in case they change the column order!). Note
also that I've converted everything to a string for the output, if you need the
file size or max size you should use oresults.GetColumnLong instead.
LogicalName : pubs
PhysicalName : C:\Program Files\Microsoft SQL Server\MSSQL$ONE\data\pubs.mdf
Type : D
FileGroupName : PRIMARY
Size : 4194304
MaxSize : 35184372080640
LogicalName : pubs_log
PhysicalName : C:\Program Files\Microsoft SQL Server\MSSQL$ONE\data\pubs_log.ldf
Type : L
FileGroupName :
Size : 786432
MaxSize : 35184372080640
|
Next we need to do the 'no recovery' thing. I was expecting to find a
property (or an option to the method), after some looking I found the
LastRestore property. Setting it to false indicates there are more files
to go - the same as no recovery really. That's when the "something
new" hit me. I had never used it before, yet my restores always worked fine
in the past. Typically a property that is not set will default based on it's
data type. A string will be empty, a number will zero, a boolean will be false.
But in this case...it defaults to true! It's probably the correct behavior in
most cases (which is why I never noticed) but all they had to do was call it
"NoRecovery" instead and they could have defaulted it to false AND
achieved the same thing. Ah well...
Anyway, this does the restore with lastrestore set to false. If you refresh
Enterprise Manager after this next batch you'll set the database state is
'Loading'.
'now do the restore - be careful, this is overwriting the existing copy
Set orestore = New SQLDMO.Restore
With orestore
.Database = "Pubs"
.Files = "C:\test.bak"
'setting this is the same as using with norecovery
.LastRestore = False
.ReplaceDatabase = True
.SQLRestore oserver
End With
Set orestore = Nothing
|
So...now I'm looking for a property or method in the restore object that will
let you do "with recovery". Not expecting to find it since it really
is a setting of the database (in sysdatabases) but in TSQL it falls under the
recover statement - so maybe? Nope. Must be a database setting. The database
object has a status property that will tell you that the db is loading, but the
property is read only. Nothing in the dboptions object that I could find either.
If someone knows how to do this via pure DMO, please tell me! Finally had to
fall back on TSQL, like this:
'if you just want to do "with recovery" this works
oserver.ExecuteImmediate "restore database pubs with recovery" |
Not a huge deal. You could also just run the restore again. Finally, just to
be a good coder clean up your object reference.
oserver.DisConnect
Set oserver = Nothing |
Questions or comments? Throw in your two cents worth! Thanks for reading this
and hope to see you posting a question (or answer) in our discussion area soon.
