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.
