从aspx页面备份和恢复SQL Server 2012数据库 [英] Backup and restore SQL server 2012 database from aspx page

问题描述

我使用sql server 2012数据库为我的应用程序存储数据

我想从我的应用程序内部对数据库进行备份和恢复

我已经实现了sqlcommand执行备份操作

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管理工作室它工作正常，但从代码后面我有一个安全错误，当前用户没有权限恢复数据库



请指导我正确的方法从后面的代码备份和恢复数据库



我尝试过：



从管理工作室它工作正常，但从代码背后我有一个安全错误，当前用户没有权限恢复数据库

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

推荐答案

你不能改变一点：你的用户没有恢复数据库的权限 - 因为这是一个破坏性的过程，这并不让我感到惊讶。

从不使用集成安全性开始：在SQL中创建用户并为他们分配您要使用的特定数据库的相应权限。一个特殊用户应该具有备份和恢复权限，并且应该只用于其他任何内容。

SQL用户应该只拥有足够的权限 - 给予他们太多是主要问题的处方如果你的代码出错了，所以不要改变通用登录用户的默认权限来解决这个问题：以后会引起你的问题。

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.

我是在最后



我启用了sql server登录模式然后启用了sa帐户并指定了密码



我已经使用sa凭证登录连接字符串并完全恢复执行

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

这篇关于从aspx页面备份和恢复SQL Server 2012数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！