More on restoring database in user instance - exclusive use not obtained

Hi all. I have read and implemented the very helpful threads on backing up and restoring with user instance posted with MFriedlander. Thank you. However, during the rs.SqlRestore(srv) command I am getting the following error "Restore failed for Server '\\.\pipe\4A1F91FF-F6FE-45\tsql\query'. "
"Exclusive access could not be obtained because the database is in use."

I have implemented the changedatabase method as described in that thread.

SqlConnection.ChangeDatabase("master")

right before the line

rs.SqlRestore(srv)

I do not use the default instance of sql express, but I do use an instance called 'test' for my app. Should my changedatabase method also refer to my sql instance?

I am running from VS 2005 debug (F5) when it fails and cannot think of anything that would be locking it. Any help would be appreciated. Thank you. Below is the full code of the restore.

Robert

Dim sqlconnection As SqlConnection = New SqlConnection(My.Settings.dbTestConnectionString)

sqlconnection.Open()

MsgBox(sqlconnection.Database.ToString())

Dim ServerConnection As ServerConnection = New ServerConnection(sqlconnection)

Dim srv As Server = New Server(ServerConnection)

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As BackupDeviceItem

bdi = New BackupDeviceItem(txtRestoreFile.Text, DeviceType.File)

''Define a Restore object variable.

Dim rs As New Restore

'Set the NoRecovery property to true, so the transactions are not recovered.

rs.NoRecovery = True

rs.ReplaceDatabase = True

'Add the device that contains the full database backup to the Restore object.

rs.Devices.Add(bdi)

'don't know why the below norecovery is changed to false

rs.NoRecovery = False

'Specify the database name.

rs.Database = sqlconnection.Database.ToString()

sqlconnection.ChangeDatabase("master")

'Restore the full database backup with no recovery.

rs.SqlRestore(srv)

Hi Robert,

I believe the problem is at

Code Snippet

rs.NoRecovery=True

It should be False, but if you read the comments about NoRecovery in BOL you'll see that True leaves the database in the recovering state, which makes it appear that it is in use the next time you try to run your Restore code. I see that you have it set in your code twice, once each way, and I'm not sure of the impact of doing that is. (It seems it would just reset it.) In any case, I'd clean the code so you're only setting NoRecovery once, and you're setting it to False.

I have a clean version of the code I wrote based on the thread you mention available in the blog, find it here. The code is acutally C#, but the SMO part is identical between the two languages once you've defined the objects.

Mike

|||

Hi Mike-

unfortunately changing the norecovery to false did not stop the error. I got it to work by changing the sqlconnection from referencing the

My.Settings.testConnectionString and replaced it with the connection string spelled out. I toggle between the 2 lines of code and the my.settings consistently doesn't work while the fully spelled out connection string works. I don't get it, I got lucky, but it works and I thank you very much for your support. The only time the restore doesn't work is immediately after running the backup code. Restarting the application fixes this and I can find an elegant way to fix this or work around for it, unless you have any ideas. Thanks again. Robert

|||

Hi Robert,

I think there may be a problem with your code...I have successfully implemented SMO.Restore in my app, and I noticed a critical difference between my restore code and yours:

My restore code (partial):

Code Snippet

Dim dbName As String

Dim SqlCon As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.UserDBConStr)

SqlCon.Open()

dbName = SqlCon.Database.ToString()

SqlCon.ChangeDatabase("master")

Dim SC As New ServerConnection(SqlCon)

Dim srv As New Smo.Server(SC)

'Verify User Instance connection

Console.WriteLine("User Instance Server: " & srv.InstanceName.ToString)

Please notice that I changed the SqlConnection database, SqlCon.ChangeDatabase("master"), before making a connection to the server using SqlCon. I believe that in your code, the SMO.Server is still using the database you are trying to restore because you made the server connection using the restore database.

Here is the rest of my restore code:

Code Snippet

Dim bdi As New Smo.BackupDeviceItem(RestoreDir, Smo.DeviceType.File)

'***Set backup details

Dim resDB As New Smo.Restore

resDB.Devices.Add(bdi)

resDB.NoRecovery = False

resDB.ReplaceDatabase = True

resDB.Database = dbName

'Run SqlBackup to perform the full database backup on the instance of SQL Server.

resDB.SqlRestore(srv)

My code is based on the guidance from Mike Waschal's blog, here

Hope that helps you.

Andre