PSCTECH
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.
the clsPublic and lvFiles.FocusedItem are just used to get the path and filename
thanks for any assistance
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
the clsPublic and lvFiles.FocusedItem are just used to get the path and filename
thanks for any assistance
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great answer. Very thorough and very fast response. Solved my problem!
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.
ASKER
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?