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

Posted on 2013-10-02
Medium Priority
Last Modified: 2013-10-02
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 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MyDB 

Open in new window

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

thanks for any assistance
Question by:PSCTECH
  • 3
  • 2
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 2000 total points
ID: 39540266
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  : )

Author Comment

ID: 39540445
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?
LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 39540457
use  this 'use statement'

USE Master
Restore .. .

Author Closing Comment

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

Author Comment

ID: 39540556
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.

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question