Hello all,

I'm having some issues with restoring databases through SMO in a vb.net application.
The restores always happen from the same SQL version, so 2000 -> 2000, 2005 -> 2005.

There are two issues I just can't seem to get fixed...
The code shown below is written to function with a central administration database that holds all SQL database information in our company, and where restores are performed on a "request" base. You'll see some objects that refering to this.

On a SQL2000 restore (the error didn't occur on SQL2005 restores so far), I get the following error, supposing I restore from database A to database B:

"File 'A_datafile' cannot be restored to 'E:\path\B_datafile.mdf'. Use WITH MOVE to identify a valid location for the file."

My code looks like:

Private sub DoFullRestore(ByVal myRequest as Request, ByVal FinalStep as Boolean)

Dim srv As New Microsoft.SqlServer.Management.Smo.Server(myRequest.TargetDatabase.FullServerName)

Dim res As New Restore

Dim backupDevice As New BackupDeviceItem(Path.Combine(myRequest.TargetDatabase.LocalCopyDirectory, Path.GetFileName(myRequest.SelectedFullBackupFile.FileName)), DeviceType.File)

res.Devices.Add(backupDevice)
res.Database = myRequest.TargetDatabase.DatabaseName
res.Action = RestoreActionType.Database
res.ReplaceDatabase = True

If FinalStep Then --> if finalstep = false, a log method is launched after this method
res.NoRecovery = False
Else
res.NoRecovery = True
End If

'Drop the target db and kill all existing connections to it

srv.Databases("master").ExecuteNonQuery(String.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", myRequest.TargetDatabase.DatabaseName))

'Restore the database to Multi-User to avoid deadlock
srv.Databases("master").ExecuteNonQuery(String.Format("ALTER DATABASE {0} SET MULTI_USER", myRequest.TargetDatabase.DatabaseName))

'Drop database, to get rid of unwanted files/filegroups
srv.Databases(myRequest.TargetDatabase.DatabaseName).Drop()

'Recreate the targetdatabase

Dim Targetdb As New Database(srv, myRequest.TargetDatabase.DatabaseName)
Targetdb.Create()

'copy the filegroups and logfile from the source database but set the filenames to the correct dirs and refer the filenames to the target database

'look up the logicalnames from the sourcedatabase
Dim sourcesrv As New Microsoft.SqlServer.Management.Smo.Server(myRequest.SourceDatabase.FullServerName)

Dim sourcedb As Database = sourcesrv.Databases(myRequest.SourceDatabase.DatabaseName)

'Indexes for each type of file, to standardize filenames
Dim dataIdx As Integer = 1
Dim logIdx As Integer = 1
Dim indexIdx As Integer = 1

For Each sfg As FileGroup In sourcedb.FileGroups

For Each df As DataFile In sfg.Files

Select Case Path.GetExtension(df.FileName.Trim)

'datafile
Case ".mdf"
Dim name As String
If dataIdx > 1 Then
name = Targetdb.Name & "_" & dataIdx.ToString
Else
name = Targetdb.Name
End If
Dim filename As String = Path.Combine(myRequest.TargetDatabase.DataDirectory, name) & ".mdf"

res.RelocateFiles.Add(New RelocateFile(df.Name, filename))

dataIdx += 1

'logfile
Case ".ldf"
Dim name As String
If logIdx > 1 Then
name = Targetdb.Name & "_log_" & logIdx.ToString
Else
name = Targetdb.Name & "_log"
End If
Dim filename As String = Path.Combine(myRequest.TargetDatabase.LogDirectory, name) & ".ldf"
res.RelocateFiles.Add(New RelocateFile(df.Name, filename))

logIdx += 1

'indexfile
Case ".ndf"
Dim name As String
If indexIdx > 1 Then
name = Targetdb.Name & "_idx_" & indexIdx.ToString
Else
name = Targetdb.Name & "_idx"
End If
Dim filename As String
If Not String.IsNullOrEmpty(myRequest.TargetDatabase.IndexDirectory) Then
filename = Path.Combine(myRequest.TargetDatabase.IndexDirectory, name) & ".ndf"
Else
filename = Path.Combine(myRequest.TargetDatabase.DataDirectory, name) & ".ndf"
End If
res.RelocateFiles.Add(New RelocateFile(df.Name, filename))
indexIdx += 1

End Select

Next
Next

'do the same for the logfiles
For Each lf As LogFile In sourcedb.LogFiles
Dim name As String
If logIdx > 1 Then
name = Targetdb.Name & "_log_" & indexIdx.ToString
Else
name = Targetdb.Name & "_log"
End If
Dim filename As String = Path.Combine(myRequest.TargetDatabase.LogDirectory, name) & ".ldf"
res.RelocateFiles.Add(New RelocateFile(lf.Name, filename))
logIdx += 1
Next

res.SqlRestore(srv)

....cleanup code

Seems like there would be a problem with the file reallocation on SQL2000?
Does anyone know what could cause it or how to solve it?


And the other issue:

If I want to do a LogRestore after this FullRestore method, I do something like this in my main method:

DoFulLRestore(currentRequest, false)
where false indicates the FinalStep value, indicating that there is a log restore being followed, so the .NoRecovery of the restore object in the fullrestore is set to True, leaving the database open for a log restore.

But when I do my log restore:

Dim srv As New Microsoft.SqlServer.Management.Smo.Server(myRequest.TargetDatabase.FullServerName)

Dim logres As New Restore

Post 2/2


And the other issue:

If I want to do a LogRestore after this FullRestore method, I do something like this in my main method:

DoFulLRestore(currentRequest, false)
where false indicates the FinalStep value, indicating that there is a log restore being followed, so the .NoRecovery of the restore object in the fullrestore is set to True, leaving the database open for a log restore.

But when I do my log restore:

Dim srv As New Microsoft.SqlServer.Management.Smo.Server(myRequest.TargetDatabase.FullServerName)

Dim logres As New Restore
Dim logbackupDevice As New BackupDeviceItem(Path.Combine(myRequest.TargetDatabase.LocalCopyDirectory, Path.GetFileName(myRequest.SelectedLogBackupFile.FileName)), DeviceType.File)
logres.Devices.Add(logbackupDevice)
logres.Database = myRequest.TargetDatabase.DatabaseName
logres.ReplaceDatabase = True

If IsPointInTimeRestore Then
'set input datetime to correct format
logres.ToPointInTime = Format(Request.PointInTime_TimeStamp, "yyyyMMdd hh:mm:ss")
End If
logres.Action = RestoreActionType.Log
logres.NoRecovery = False

'start the log restore

logres.SqlRestore(srv)


Then I get the error: Database 'A' cannot be opened. It is in the middle of a restore.
And it is kept in that status, untill I drop that database and recreate it in SSMS...

Any clues on this one?

Any help is already appreciated!

-- Wouter




From http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=113&threadid=1053881

Posted via DevelopmentNow.com Groups