SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Restoring Using DMO - Getting File List and No Recovery

By Andy Warren,

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

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.

Total article views: 8485 | Views in the last 30 days: 2
Related Articles

Using DMO to Restore a Database

This article by Andy Warren includes code samples showing you how to restore a database from a file ...


Database Restoration

Unnecessary database restoration


restore database

restore database


Restoring Database

Restoring Database


Restore (database onwer)

Restore (database onwer)

backup and restore    
sql server 7    
visual basic 6