SQLServerCentral Article

Restoring Using DMO - Getting File List and No Recovery


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)



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


'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.


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.


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating