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)permissions
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
PSCTECHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Why do you need to create another application for this (SSMS has this functionality). as far as permission is concerned, your application user must have sysadmin roles. also should have right to access the back up location and write permission on data drives.  
If some one else gets this application, you can imagine what's goanna happen  : )
0
PSCTECHAuthor Commented:
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?
0
Aneesh RetnakaranDatabase AdministratorCommented:
use  this 'use statement'

USE Master
go
Restore .. .
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PSCTECHAuthor Commented:
Great answer.  Very thorough and very fast response.  Solved my problem!
0
PSCTECHAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.