joe joe - 1 year ago 104 Question

RESTORE DATABASE is terminating abnormally

I've managed to write a function that allows me to backup a SQL Server database, but I'm struggling with trying with restoring and using the backed up version.

My code:

Dim confirmBackUp As MsgBoxResult
confirmBackUp = MsgBox("Are you sure you want to restore?")

If confirmBackUp = MsgBoxResult.Yes Then Else Exit Sub
Dim cmd As New OleDbCommand
con = New OleDbConnection()

' con.Connectionstring is read from an .ini file, but the string is correct

cmd.CommandType = CommandType.Text
cmd.CommandText = "RESTORE DATABASE MaintenanceControl FROM DISK='c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\MaintenanceControl.bak'"
cmd.Connection = con

MsgBox("Database Restored", MsgBoxStyle.OkOnly, "Success")

Catch ex As Exception
errorLog(ex.Message, ex.StackTrace)
MsgBox("Could not restore database, refer to error log")

End Try

But on the
line, I get the following error;

RESTORE DATABASE is terminating abnormally.

RESTORE cannot process database 'MaintenanceControl' because it is in use by this session. It is recommended that the master database be used when performing this operation.

What is the issue? Is it because con is also the name of the connection to the live database and it shouldn't be given a new string?


I've now set the
Initial Catalog
part of the connection to 'Master', as well as added con.Close() before
con = New OleDbconnection
however the error I now get is

RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use.

Answer Source

If you're ok with dropping and recreating the database, you can use this statement, which will allow you to drop it:

alter database MaintenanceControl set single_user with rollback immediate; 
drop database SomeDatMaintenanceControl base;

and then create the database from scratch as you need.

Dropping the database will destroy the database so that there's no possibility that someone is connected to it while you're trying to restore. Then you create the database cleanly.