Click here to Skip to main content
15,900,907 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I use sql server 2012 database to store data for my application
I want to make a backup and restore for the database from inside my application
I've Implemented sqlcommand to execute backup operation
VB
Using Cmd As New SqlCommand() With {.Connection = New SqlConnection(ConfigurationManager.ConnectionStrings("JoyBoxArchiveCn").ConnectionString)}

            Dim a = New SqlConnection
            Dim Fnm As String = Server.MapPath("~\backup\test001.bak")
           ' Backup Code
            Cmd.CommandText = "backup database jbxarchive to disk='" & Fnm & "'"
           Cmd.Connection.Open()
           r = Cmd.ExecuteNonQuery
           Cmd.Connection.Close()

and that does it ok
the problem is when I've tried to restore
VB
Cmd.CommandText = "alter database jbxarchive set single_user with rollback immediate; "
           Cmd.CommandText &= "restore database jbxarchive from disk='" & Fnm & "' with replace;"
           Cmd.CommandText &= "alter database jbxarchive set multi_user;"

from management studio it works fine but from code behind I've got a security error that the current user does not have permission to restore database

please instruct me the correct way to backup and restore database from code behind

What I have tried:

from management studio it works fine but from code behind I've got a security error that the current user does not have permission to restore database
Posted
Updated 2-Nov-16 21:48pm
v2
Comments
[no name] 2-Nov-16 9:14am    
The error message seems to be perfectly clear. What is it that you did not understand?
samerselo 2-Nov-16 9:17am    
I understand the error message

I need Solution

the connection string uses itegrated security not username and password
[no name] 2-Nov-16 9:22am    
Then you do not understand the error message at all. Did you give permission to the user in your database to allow them to restore?

You can't change that: your user doesn't not have permission to restore a database - since this is a destructive process, that doesn't surprise me.
Start off by not using integrated security: create users in SQL and assign them the appropriate permissions on the specific database you want to use. One -special- user should have backup and restore permissions and should be used for nothing else.
SQL users should only ever have "just-enough" permissions - giving them too much is a recipe for major problems if your code goes wrong, so don't change the default permissions on a "generic login" user to get round this: it will cause you untold problems later on.
 
Share this answer
 
I did it at last

I've enabled the sql server login mode then enabled the sa account and specify password

I've used sa credentials to login in the connection string and restore executed perfectly
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900