asked on

Can't restore SQL Server 2008 db from VB.NET app

I have a VB.NET app that connects to a SQL db.
I want to be able to create a backup, change the data and then restore the data to the pre-changed state.

I am able to create a backup file, but when I try to restore the file, I get the following error:
User does not have permission to RESTORE database 'MyDB'.
RESTORE DATABASE is terminating abnormally.

In SMSS I have given the application all permissions: (see image)

I think I probably have to disconnect from the database 1st, but I'm not getting a "db in use error" just the permission error.

Here is the code I'm using in my VB.NET app for the restore.

RESTORE DATABASE MyDB FROM disk= '" & clsPublic.strPSCBakDir & 
lvFiles.FocusedItem.Text & "'  WITH MOVE 'MyDB ' TO 'C:\Program Files\Microsoft SQL 
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MyDB .mdf', MOVE 'MyDB _Log' TO 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MyDB 
_Log.ldf',replace

Open in new window

the clsPublic and lvFiles.FocusedItem are just used to get the path and filename

thanks for any assistance

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PSCTECH

ASKER

Thanks, the sysadmin seemed to get me past the error now I get:

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

I assume that means I have to disconnect from MyDB from within the app. What is the syntax for that?

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PSCTECH

ASKER

Great answer. Very thorough and very fast response. Solved my problem!

PSCTECH

ASKER

Thank you, you saved my (_:_)! The Use Master worked. The go caused a syntax error (not sure why) but using just the Use Master fixed it! Thanks again.