Below is the query that I am using to backup (create a .bak
) my database.
下面是我用于备份(创建.bak)数据库的查询。
However, whenever I run it, I always get this error message:
然而,每当我运行它时,总是会得到这个错误消息:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\Users\Me\Desktop\Backup\MyDB.Bak'. Operating system error 5(Access is denied.).mg3201,第16级,状态1,第1行不能打开备份设备的C:\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \。操作系统错误5(访问被拒绝)。
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.Msg 3013,第16级,状态1,第1行备份数据库终止异常。
This is my query:
这是我的查询:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:\Users\Me\Desktop\Backup\MyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';
Thanks in advance.
提前谢谢。
16 个解决方案
#1
184
Yeah I just scored this one.
是的,我刚刚进了这个球。
Look in Windows Services. Start > Administration > Services
看在Windows服务。启动>管理>服务。
Find the Service in the list called: SQL Server (MSSQLSERVER) look for the "Log On As" column (need to add it if it doesn't exist in the list).
在名为:SQL Server (MSSQLSERVER)的列表中查找该服务,查找“作为”列的“登录”(如果列表中不存在,则需要添加它)。
This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)
这是您需要给目录授予权限的帐户,右键单击explorer >属性>共享(和安全性)
NOTE: Remember to give permissions to the actual directory AND to the share if you are going across the network.
注意:如果您要通过网络,请记住将权限授予实际的目录和共享。
Apply and wait for the permissions to propogate, try the backup again.
申请并等待许可的申请,再试一次备份。
NOTE 2: if you are backing up across the network and your SQL is running as "Local Service" then you are in trouble ... you can try assigning permissions or it may be easier to backup locally and xcopy across outside of SQL Server (an hour later).
注意2:如果您正在跨网络备份,而您的SQL作为“本地服务”运行,那么您就有麻烦了……您可以尝试分配权限,或者在SQL Server的外部(一个小时后)在本地和xcopy之间进行备份。
NOTE 3: If you're running as network service then SOMETIMES the remote machine will not recognize the network serivce on your SQL Server. If this is the case you need to add permissions for the actual computer itself eg. MyServer$.
注释3:如果您以网络服务的形式运行,那么有时远程机器将无法识别您的SQL服务器上的网络serivce。如果是这种情况,您需要为实际的计算机本身添加权限。MyServer美元。
#2
9
Go to the SQL server folder in start menu and click configuration tools Select SQL Server configuration manager On SQL server services, on the desired instance change the (Log On as) to local system
转到“开始”菜单中的“SQL server”文件夹,并单击“配置工具”,在SQL server服务上选择SQL server configuration manager,在所需的实例上将(Log On)更改为本地系统。
#3
7
The SQL Server service account does not have permissions to write to the folder C:\Users\Kimpoy\Desktop\Backup\
SQL Server服务帐户没有权限将其写入到文件夹c:userskimpoydesktopbackup\。
#4
6
I had this issue recently as well, however I was running the backup job from server A but the database being backed up was on server B to a file share on server C. When the agent on server A tells server B to run a backup t-sql command, its actually the service account that sql is running under on SERVER B that attempts to write the backup to server C.
最近我有这个问题,不过我从服务器是运行备份的工作但被备份的数据库服务器B上的文件共享服务器C .当代理服务器告诉服务器B运行备份t - sql命令,它实际上sql的服务帐户下运行在服务器B试图写备份服务器C。
Just remember, its the service account of the sql server performing the actual BACKUP DATABASE command is what needs privileges on the file system, not the agent.
请记住,执行实际备份数据库命令的sql服务器的服务帐户是在文件系统上需要特权,而不是代理。
#5
4
I was just going through this myself. I had ensured that my MSSQLSERVER login user had full access
but it was still causing issues. It only worked once I moved the destination to the root of C. More importantly out of a user folder (even though I had a share with full permissions - even tried "Everyone" as a test).
我自己也在经历这一切。我确保了我的MSSQLSERVER登录用户有完全访问权限,但仍然引起问题。只有当我将目的地移到c的根目录时,它才会起作用。更重要的是,我从一个用户文件夹中(尽管我拥有一个完全权限的共享),甚至尝试了“每个人”作为测试。
I don't know if i consider my issue "fixed", however it is "working".
我不知道我是否认为我的问题是“固定的”,不管它是“工作的”。
Just a FYI for any other users that come across this thread.
对于其他遇到此线程的用户,只需要一个FYI即可。
#6
3
I face the same problem with SQL Express 2014 SP1 on Windows 10.
我在Windows 10上的SQL Express 2014 SP1面临同样的问题。
Solution which work
解决方案工作
- Open Service by typing Services
- 通过键入服务来打开服务。
- Locate and open the SQL Server (SQLExpress)
- 找到并打开SQL Server (SQLExpress)
- Go to the LogOn Tab
- 转到登录选项卡。
- Choose Local System Account ( Also Check for Allow Services to interact with desktop )
- 选择本地系统帐户(也要检查是否允许服务与桌面交互)
- Click OK . Stop the service . Restart the service.
- 单击OK。停止服务。重新启动服务。
- Problem solved
- 问题解决了
#7
2
I had a similar issue. I added write permissions to the .bak file itself, and my folder that I was writing the backup to for the NETWORK SERVICE user. To add permissions just right-click what file/directory you want to alter, select the security tab, and add the appropriate users/permissions there.
我有一个类似的问题。我添加了对.bak文件本身的写权限,以及我为网络服务用户编写备份的文件夹。要添加权限,只需右键单击要修改的文件/目录,选择security选项卡,并在那里添加适当的用户/权限。
#8
2
Here is what I did to by-pass the issue.
这就是我所做的。
1) Go to backup
1)去备份
2) Remove the destination file-path to disk
2)删除目标文件路径到磁盘。
3) Click on Add
3)点击添加
4) In the File name: check box manually type in the backup name after ..\backup like below where Yourdb.bak is the database backup name
4)在文件名中:复选框手动输入后的备份名称。\备份,如下面的数据库。bak是数据库的备份名。
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Yourdb.bak
C:\Program Files\Microsoft SQL Server \ MSSQL11.MSSQLSERVER \该软件\ \ Yourdb.bak备份
5) Click on OK
5)单击OK
Hope this helps!
希望这可以帮助!
#9
1
I had the same issue and the url below really helped me.
我有同样的问题,下面的url真的帮助了我。
It might help you as well.
这对你也有帮助。
http://blog.sqlauthority.com/2011/04/13/sql服务器-修复错误味精- 3201 - 16 -不能开放级备份-设备-操作系统- 5 -访问denied/错误
#10
1
I know it is not an exact solution but using external drive paths solves this problem.
我知道这不是一个精确的解决方案,但是使用外部驱动路径解决了这个问题。
BACKUP DATABASE AcinsoftDB
TO DISK = 'E:\MyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';
#11
0
Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'C:\Backup\Adventure_20120720_1024AM.trn'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally.
mg3201,第16级,状态1,第1行不能打开备份设备的C:\ backup \Adventure _20120720_ 1024AM.trn。操作系统错误5(访问被拒绝)。Msg 3013,第16级,状态1,第1行备份日志终止异常。
I verified backup folder on C drive, Is new service account is having full control access permission or not?, I realized that "Test\Kiran" service account is not having Full control security permission.
我验证了C驱动器上的备份文件夹,新服务帐户是否有完全控制访问权限?我意识到“测试\Kiran”服务帐户没有完全的控制安全权限。
Please follow the below steps to give full control to service account:
请按照以下步骤,全面控制服务帐户:
- Go to C drive, Right click on Backup folder.
- 转到C驱动器,右键单击备份文件夹。
- Select Security tab.
- 选择安全选项卡。
- Click on Edit button, new window will open.
- 点击编辑按钮,新窗口将打开。
- Click on Add button and enter Test\Kiran user account and click check name button, this will validate you entered user is existing or not, if it is existing it will show the user on window, select OK.
- 点击Add按钮,进入Test\Kiran用户帐户,点击check name按钮,这将验证您输入的用户是否存在,如果存在,将显示用户在窗口,选择OK。
- Select you entered user name and select Full Control check box under allow.
- 选择您输入用户名并在允许的情况下选择完全控制复选框。
#12
0
Please check the access to drives.First create one folder and go to folder properties ,
请检查驱动器的访问权限。首先创建一个文件夹并进入文件夹属性,
You may find the security tab ,click on that check whether your user id having the access or not.
您可以找到安全选项卡,单击该选项检查您的用户是否具有访问权限。
if couldn't find the your id,please click the add buttion and give user name with full access.
如果找不到您的id,请点击add buttion,并给用户名提供完整的访问权限。
#13
0
Share this folder and use UNC path, by example: \pc\backups\mydb.bak
共享此文件夹并使用UNC路径,例如:\pc\备份\mydb.bak。
Then You can stop share.
然后你可以停止分享。
Not very elegant, but it resolves all permissions problems (You need to give permissions to share as well, as mentioned above)
不是很优雅,但是它解决了所有的权限问题(您需要提供共享的权限,如上所述)
#14
0
I experienced this problem when the .BAK file was temporarily stored in a folder encrypted with BitLocker. It retained the encryption after it was moved to a different folder.
当. bak文件临时存储在用BitLocker加密的文件夹中时,我就遇到了这个问题。它在移动到另一个文件夹后保留了加密。
The NETWORK SERVICE account was unable to decrypt the file and gave this thoroughly informative error message.
网络服务帐户无法对文件进行解密,并提供了这一非常有用的错误消息。
Removing BitLocker encryption (by unchecking "Encrypt contents to secure data" in the file properties) on the .BAK file resolved the issue.
删除BitLocker加密(通过在文件属性中不检查“加密内容以获取数据”)解决了这个问题。
#15
0
I have the same error. Following changes helped me to fix this.
我有同样的错误。以下的变化帮助我解决了这个问题。
I had to check Server Manager->Tool->Services and find the user ("Log On As" column) for service: SQL Server (SQLEXPRESS).
我必须检查服务器管理器->工具->服务,并找到用户(“登录为”列)用于服务:SQL Server (SQLEXPRESS)。
I went to the local folder (C:\Users\Me\Desktop\Backup) and added "NT Service\MSSQL$SQLEXPRESS" as the user to give Write permissions.
我到本地的文件夹(C:\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \的\ \ \ \ \ \ \ \ \ \ \ \
#16
0
Hi you need to change the query from:
你好,你需要更改查询:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:\Users\Me\Desktop\Backup\MyDB.Bak'
to
来
BACKUP DATABASE AcinsoftDB
TO DISK = N'C:\Users\Me\Desktop\Backup\MyDB.Bak'
You have to add a N in front of the path works for me.
你必须在路径前面加上一个N。
#1
184
Yeah I just scored this one.
是的,我刚刚进了这个球。
Look in Windows Services. Start > Administration > Services
看在Windows服务。启动>管理>服务。
Find the Service in the list called: SQL Server (MSSQLSERVER) look for the "Log On As" column (need to add it if it doesn't exist in the list).
在名为:SQL Server (MSSQLSERVER)的列表中查找该服务,查找“作为”列的“登录”(如果列表中不存在,则需要添加它)。
This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)
这是您需要给目录授予权限的帐户,右键单击explorer >属性>共享(和安全性)
NOTE: Remember to give permissions to the actual directory AND to the share if you are going across the network.
注意:如果您要通过网络,请记住将权限授予实际的目录和共享。
Apply and wait for the permissions to propogate, try the backup again.
申请并等待许可的申请,再试一次备份。
NOTE 2: if you are backing up across the network and your SQL is running as "Local Service" then you are in trouble ... you can try assigning permissions or it may be easier to backup locally and xcopy across outside of SQL Server (an hour later).
注意2:如果您正在跨网络备份,而您的SQL作为“本地服务”运行,那么您就有麻烦了……您可以尝试分配权限,或者在SQL Server的外部(一个小时后)在本地和xcopy之间进行备份。
NOTE 3: If you're running as network service then SOMETIMES the remote machine will not recognize the network serivce on your SQL Server. If this is the case you need to add permissions for the actual computer itself eg. MyServer$.
注释3:如果您以网络服务的形式运行,那么有时远程机器将无法识别您的SQL服务器上的网络serivce。如果是这种情况,您需要为实际的计算机本身添加权限。MyServer美元。
#2
9
Go to the SQL server folder in start menu and click configuration tools Select SQL Server configuration manager On SQL server services, on the desired instance change the (Log On as) to local system
转到“开始”菜单中的“SQL server”文件夹,并单击“配置工具”,在SQL server服务上选择SQL server configuration manager,在所需的实例上将(Log On)更改为本地系统。
#3
7
The SQL Server service account does not have permissions to write to the folder C:\Users\Kimpoy\Desktop\Backup\
SQL Server服务帐户没有权限将其写入到文件夹c:userskimpoydesktopbackup\。
#4
6
I had this issue recently as well, however I was running the backup job from server A but the database being backed up was on server B to a file share on server C. When the agent on server A tells server B to run a backup t-sql command, its actually the service account that sql is running under on SERVER B that attempts to write the backup to server C.
最近我有这个问题,不过我从服务器是运行备份的工作但被备份的数据库服务器B上的文件共享服务器C .当代理服务器告诉服务器B运行备份t - sql命令,它实际上sql的服务帐户下运行在服务器B试图写备份服务器C。
Just remember, its the service account of the sql server performing the actual BACKUP DATABASE command is what needs privileges on the file system, not the agent.
请记住,执行实际备份数据库命令的sql服务器的服务帐户是在文件系统上需要特权,而不是代理。
#5
4
I was just going through this myself. I had ensured that my MSSQLSERVER login user had full access
but it was still causing issues. It only worked once I moved the destination to the root of C. More importantly out of a user folder (even though I had a share with full permissions - even tried "Everyone" as a test).
我自己也在经历这一切。我确保了我的MSSQLSERVER登录用户有完全访问权限,但仍然引起问题。只有当我将目的地移到c的根目录时,它才会起作用。更重要的是,我从一个用户文件夹中(尽管我拥有一个完全权限的共享),甚至尝试了“每个人”作为测试。
I don't know if i consider my issue "fixed", however it is "working".
我不知道我是否认为我的问题是“固定的”,不管它是“工作的”。
Just a FYI for any other users that come across this thread.
对于其他遇到此线程的用户,只需要一个FYI即可。
#6
3
I face the same problem with SQL Express 2014 SP1 on Windows 10.
我在Windows 10上的SQL Express 2014 SP1面临同样的问题。
Solution which work
解决方案工作
- Open Service by typing Services
- 通过键入服务来打开服务。
- Locate and open the SQL Server (SQLExpress)
- 找到并打开SQL Server (SQLExpress)
- Go to the LogOn Tab
- 转到登录选项卡。
- Choose Local System Account ( Also Check for Allow Services to interact with desktop )
- 选择本地系统帐户(也要检查是否允许服务与桌面交互)
- Click OK . Stop the service . Restart the service.
- 单击OK。停止服务。重新启动服务。
- Problem solved
- 问题解决了
#7
2
I had a similar issue. I added write permissions to the .bak file itself, and my folder that I was writing the backup to for the NETWORK SERVICE user. To add permissions just right-click what file/directory you want to alter, select the security tab, and add the appropriate users/permissions there.
我有一个类似的问题。我添加了对.bak文件本身的写权限,以及我为网络服务用户编写备份的文件夹。要添加权限,只需右键单击要修改的文件/目录,选择security选项卡,并在那里添加适当的用户/权限。
#8
2
Here is what I did to by-pass the issue.
这就是我所做的。
1) Go to backup
1)去备份
2) Remove the destination file-path to disk
2)删除目标文件路径到磁盘。
3) Click on Add
3)点击添加
4) In the File name: check box manually type in the backup name after ..\backup like below where Yourdb.bak is the database backup name
4)在文件名中:复选框手动输入后的备份名称。\备份,如下面的数据库。bak是数据库的备份名。
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Yourdb.bak
C:\Program Files\Microsoft SQL Server \ MSSQL11.MSSQLSERVER \该软件\ \ Yourdb.bak备份
5) Click on OK
5)单击OK
Hope this helps!
希望这可以帮助!
#9
1
I had the same issue and the url below really helped me.
我有同样的问题,下面的url真的帮助了我。
It might help you as well.
这对你也有帮助。
http://blog.sqlauthority.com/2011/04/13/sql服务器-修复错误味精- 3201 - 16 -不能开放级备份-设备-操作系统- 5 -访问denied/错误
#10
1
I know it is not an exact solution but using external drive paths solves this problem.
我知道这不是一个精确的解决方案,但是使用外部驱动路径解决了这个问题。
BACKUP DATABASE AcinsoftDB
TO DISK = 'E:\MyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';
#11
0
Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'C:\Backup\Adventure_20120720_1024AM.trn'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally.
mg3201,第16级,状态1,第1行不能打开备份设备的C:\ backup \Adventure _20120720_ 1024AM.trn。操作系统错误5(访问被拒绝)。Msg 3013,第16级,状态1,第1行备份日志终止异常。
I verified backup folder on C drive, Is new service account is having full control access permission or not?, I realized that "Test\Kiran" service account is not having Full control security permission.
我验证了C驱动器上的备份文件夹,新服务帐户是否有完全控制访问权限?我意识到“测试\Kiran”服务帐户没有完全的控制安全权限。
Please follow the below steps to give full control to service account:
请按照以下步骤,全面控制服务帐户:
- Go to C drive, Right click on Backup folder.
- 转到C驱动器,右键单击备份文件夹。
- Select Security tab.
- 选择安全选项卡。
- Click on Edit button, new window will open.
- 点击编辑按钮,新窗口将打开。
- Click on Add button and enter Test\Kiran user account and click check name button, this will validate you entered user is existing or not, if it is existing it will show the user on window, select OK.
- 点击Add按钮,进入Test\Kiran用户帐户,点击check name按钮,这将验证您输入的用户是否存在,如果存在,将显示用户在窗口,选择OK。
- Select you entered user name and select Full Control check box under allow.
- 选择您输入用户名并在允许的情况下选择完全控制复选框。
#12
0
Please check the access to drives.First create one folder and go to folder properties ,
请检查驱动器的访问权限。首先创建一个文件夹并进入文件夹属性,
You may find the security tab ,click on that check whether your user id having the access or not.
您可以找到安全选项卡,单击该选项检查您的用户是否具有访问权限。
if couldn't find the your id,please click the add buttion and give user name with full access.
如果找不到您的id,请点击add buttion,并给用户名提供完整的访问权限。
#13
0
Share this folder and use UNC path, by example: \pc\backups\mydb.bak
共享此文件夹并使用UNC路径,例如:\pc\备份\mydb.bak。
Then You can stop share.
然后你可以停止分享。
Not very elegant, but it resolves all permissions problems (You need to give permissions to share as well, as mentioned above)
不是很优雅,但是它解决了所有的权限问题(您需要提供共享的权限,如上所述)
#14
0
I experienced this problem when the .BAK file was temporarily stored in a folder encrypted with BitLocker. It retained the encryption after it was moved to a different folder.
当. bak文件临时存储在用BitLocker加密的文件夹中时,我就遇到了这个问题。它在移动到另一个文件夹后保留了加密。
The NETWORK SERVICE account was unable to decrypt the file and gave this thoroughly informative error message.
网络服务帐户无法对文件进行解密,并提供了这一非常有用的错误消息。
Removing BitLocker encryption (by unchecking "Encrypt contents to secure data" in the file properties) on the .BAK file resolved the issue.
删除BitLocker加密(通过在文件属性中不检查“加密内容以获取数据”)解决了这个问题。
#15
0
I have the same error. Following changes helped me to fix this.
我有同样的错误。以下的变化帮助我解决了这个问题。
I had to check Server Manager->Tool->Services and find the user ("Log On As" column) for service: SQL Server (SQLEXPRESS).
我必须检查服务器管理器->工具->服务,并找到用户(“登录为”列)用于服务:SQL Server (SQLEXPRESS)。
I went to the local folder (C:\Users\Me\Desktop\Backup) and added "NT Service\MSSQL$SQLEXPRESS" as the user to give Write permissions.
我到本地的文件夹(C:\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \的\ \ \ \ \ \ \ \ \ \ \ \
#16
0
Hi you need to change the query from:
你好,你需要更改查询:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:\Users\Me\Desktop\Backup\MyDB.Bak'
to
来
BACKUP DATABASE AcinsoftDB
TO DISK = N'C:\Users\Me\Desktop\Backup\MyDB.Bak'
You have to add a N in front of the path works for me.
你必须在路径前面加上一个N。