locked
Backup and restore Sql Server 2012 Database from aspx Page RRS feed

  • Question

  • User1927181471 posted

    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 

    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

            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

    Wednesday, November 2, 2016 12:56 PM

Answers

  • User-654786183 posted

    Are you using the same credentials to connect from SSMS and your ASP.NET application.  If not, as per MSDN

    Permissions

    If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

    RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2016 1:16 PM
  • User1927181471 posted

    Thnak you

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 3, 2016 7:45 AM

All replies

  • User-654786183 posted

    Are you using the same credentials to connect from SSMS and your ASP.NET application.  If not, as per MSDN

    Permissions

    If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

    RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2016 1:16 PM
  • User1927181471 posted

    I use integrated security to connect bcz my application works on private network

    my app is installed on windows server 2012 iis 7.5 and I use the sql express edition 2012

    I'll see your reply and try to solve the problem thank you

    Thursday, November 3, 2016 6:44 AM
  • User1927181471 posted

    Thnak you

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 3, 2016 7:45 AM