I am starting to learn SQL and I have a book that provides a database to work on. These files below are in the directory but the problem is that when I run the query, it gives me this error:
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file
«C:MurachSQL Server 2008DatabasesAP.mdf». Operating system error
5: «5(Access is denied.)».
CREATE DATABASE AP
ON PRIMARY (FILENAME = 'C:MurachSQL Server 2008DatabasesAP.mdf')
LOG ON (FILENAME = 'C:MurachSQL Server 2008DatabasesAP_log.ldf')
FOR ATTACH
GO
In the book the author says it should work, but it is not working in my case. I searched but I do not know exactly what the problem is, so I posted this question.
MAbraham1
1,7174 gold badges28 silver badges45 bronze badges
asked Aug 17, 2013 at 8:30
1
SQL Server database engine service account must have permissions to read/write in the new folder.
Check out this
To fix, I did the following:
Added the Administrators Group to the file security permissions with
full control for the Data file (S:) and the Log File (T:).Attached the database and it works fine.
answered Aug 17, 2013 at 8:37
Rahul TripathiRahul Tripathi
167k31 gold badges277 silver badges331 bronze badges
12
An old post, but here is a step by step that worked for SQL Server 2014 running under windows 7:
- Control Panel ->
- System and Security ->
- Administrative Tools ->
- Services ->
- Double Click SQL Server (SQLEXPRESS) -> right click, Properties
- Select Log On Tab
- Select «Local System Account» (the default was some obtuse Windows System account)
- -> OK
- right click, Stop
- right click, Start
Voilá !
I think setting the logon account may have been an option in the installation, but if so it was not the default, and was easy to miss if you were not already aware of this issue.
answered Feb 17, 2016 at 18:38
10
To get around the access denied issue, I started SSMS as administrator and that allowed me to attach a database from my local drive. The database was created in another SQL and windows instance.
answered Feb 23, 2015 at 11:13
JayKayOf4JayKayOf4
1,2021 gold badge12 silver badges15 bronze badges
4
This is Windows related issue where SQL Server does not have the appropriate permission to the folder that contains .bak file and hence this error.
The easiest work around is to copy your .bak file to default SQL backup location which has all the necessary permissions. You do not need to fiddle with anything else. In SQL SERVER 2012, this location is
D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackup (SQL 2012)
C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackup (SQL 2014)
C:Program FilesMicrosoft SQL ServerMSSQL13.SQLEXPRESSMSSQLBackup (SQL 2016)
answered Oct 9, 2014 at 8:25
TheTechGuyTheTechGuy
16.4k16 gold badges114 silver badges135 bronze badges
6
I had this problem. Just run SQL Server as administrator
Adrian Mole
49.5k155 gold badges49 silver badges79 bronze badges
answered Jul 5, 2017 at 0:55
3
Yes,It’s right.The first you should find out your service account of sqlserver,you can see it in Task Manager when you press ctrl+alt+delete at the same time;Then,you must give the read/write privilege of «C:MurachSQL Server 2008Databases» to the service account.
answered Aug 17, 2013 at 8:48
TimTim
1491 silver badge9 bronze badges
0
I solve this problem by adding Full control
permission for both .mdf
and .ldf
files for Users
group.
answered Dec 2, 2016 at 15:26
1
The problem is due to lack of permissions for SQL Server to access the mdf & ldf files. All these procedures will work :
- you can directly change the MSSQLSERVER service startup user account, with the user account who have better privileges on the files. Then try to attach the database.
- Or you can assign the user to the file in security tab of the mdf & ldf files properties with read and and write privileges checked.
- Startup with windows administrator account, and open SQL Server with run as administrator option and try to login with windows authentication and now try to attach the database.
Manu
4,3746 gold badges43 silver badges77 bronze badges
answered Mar 13, 2015 at 12:12
For me it was solved in the following way with SQL Server Management studio
-Log in as admin (I logged in as windows authentication)
-Attach the mdf file (right click Database | attach | Add )
-Log out as admin
-Log in as normal user
answered May 23, 2016 at 13:03
0
The actual server permissions will not matter at this point; all looks ok.
SQL Server itself needs folder permissions.
depending on your version, you can add SERVERNAME$MSSQLSERVER permissions to touch your folder. Othewise, it has to be in the default BACKUP directory (either where you installed it or default to c:programfiles(x)MSSQLBACKUP.
answered Sep 11, 2014 at 19:27
ClaudiaClaudia
511 silver badge1 bronze badge
Even if you do the following steps you COULD get the same error message.
1. login as SA user (SSMS)
2. Edit the file permissions to say "everyone" full access (windows folder)
3. Delete the Log file (Windows Exploring (this was what I had done per advise from some msdn forum)
I still GOT the permission error, but then I noticed that in the Attach screen, the bottom section STILL showed the LOG file, and the error message remained the same.
Hope this helps someone who did the same thing.
answered May 21, 2014 at 0:18
Tom StickelTom Stickel
19.5k6 gold badges111 silver badges113 bronze badges
It means the SSMS login user does not have permission on the .mdf file. This is how it has worked for me:
I had opened the SSMS (Run as administrator) and login as an administrator user, database right-click attach, click add, select the .mdf file, click Ok. Done.
answered Jul 15, 2020 at 22:44
GoldfishGoldfish
5845 silver badges11 bronze badges
I had this issue when I try to backup a database.
System.Data.SqlClient.SqlError:
Cannot open backup device 'C:xxxxx.bak'.
Operating system error 5 (Access is denied.). (Microsoft.SqlServer.Smo)
When I had this issue I thought that the user which I’m connecting to database don’t have rights to access to the backup location. I gave full control to that user but nothing changed. This is because the service for SQL Server is running with another user.
At this point you may choose changing user of the service to local system account or add access rights for the current user of the service.
I choose the 2nd one.
After that change backup succeeded.
answered May 20, 2022 at 9:47
Ozan BAYRAMOzan BAYRAM
2,6871 gold badge27 silver badges34 bronze badges
1
Very Simple Solution.
- Login with System admin
- copy your mdf and ldf files in «C:Program Files (x86)Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA» Where all other data file recides.
- Now attach from there it will work
Draken
3,13413 gold badges33 silver badges53 bronze badges
answered Sep 20, 2016 at 10:19
1
I used Entity framework in my application and had this problem,I seted any permission in folders and windows services and not work,
after that I start my application as administrator (right click in exe file and select «run as admin») and that works fine.
answered Jul 17, 2017 at 7:45
Ali YousefiAli Yousefi
2,3552 gold badges32 silver badges47 bronze badges
If you get this error on an .MDF
file in the APP_DATA
folder (or where ever you put it) for a Visual Studio project, the way I did it was to simply copy permissions from the existing DATA
folder here (I’m using SQL Express 2014 to support an older app):
C:Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESS2014MSSQLDATA
(note: your actual install path my vary — especially if your instance name is different)
Double click on theDATA
folder first as administrator to make sure you have access, then open the properties on the folder and mimic the same for the APP_DATA
folder. In my case the missing user was MSSQL$SQLEXPRESS2014
(because I named the instance SQLEXPRESS2014
— yours may be different). That also happens to be the SQL Server service username.
answered Jan 24, 2019 at 1:37
James WilkinsJames Wilkins
6,7252 gold badges47 silver badges73 bronze badges
For some reason, setting all the correct permissions did not help in my case. I had a file db.bak
that I was not able to restore due to the 5(Access is denied.)
error. The file was placed in the same folder as several other backup files and all the permissions were identical to other files. I was able to restore all the other files except this db.bak
file. I even tried to change the SQL Server service log on user — still the same result. I’ve tried copying the file with no effect.
Then I attempted to just create an identical file by executing
type db.bak > db2.bak
instead of copying the file. And voila it worked! db2.bak
restored successfully.
I suspect that some other problems with reading the backup file may be erroniously reported as 5(Access is denied.)
by MS SQL.
answered Nov 27, 2019 at 8:49
ps_ttfps_ttf
1,0967 silver badges15 bronze badges
In linux, I went to /var/opt/mssql/data/
folder and opened a terminal with sudo
then, changed my *.mdf and *.ldf file permissions as below in which you replace yourDB
with your Database file name and myUser
to currently logged username:
chmod 755 yourDB.mdf
chown myUser yourDB.mdf
chmod 755 yourDB.ldf
chown myUser yourDB.ldf
After that, it was reconnected without any issue.
answered Jan 8, 2020 at 8:07
If the database you are trying to attach is compressed it may show error message.
First you have to decompress the file. For that go to properties of mdf/ldf file >> then «Advanced» >> Uncheck «Compress Contents to save disk space» >> Press «Apply».
After that give it a try.
Dharman♦
30.4k22 gold badges84 silver badges132 bronze badges
answered May 20, 2021 at 19:47
1
- Run SQL Server management studio as Administrator
- Log in as Windows user
- Remove log file if you have only MDF file (haven’t log file)
With 3 items in the above checklist, you will remove almost problems related with attach database task.
answered May 22, 2021 at 5:17
If you’re using Storage Gateway — SMB (S3)
Do this from the management studio
-
EXEC xp_cmdshell ‘net use X: 100.155.16.6mystoragegatewayfolder xxmysuperpassxx /user:sgw-445577smbguest /persistent:yes /y’
-
EXEC XP_CMDSHELL ‘Dir X:’ (this should show you the Directory info, serial no etc)
-
Mount the drive (This PC — > Mount network drive, using the same info above)
Test run a backup job using scheduled backup, this will force to use the sql server agent and you can see where it’s writing and whats the issue if any.
answered Aug 9, 2022 at 4:51
Below is the query that I am using to backup (create a .bak
) my database.
However, whenever I run it, I always get this error message:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:UsersMeDesktopBackupMyDB.Bak’. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
This is my query:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:UsersMeDesktopBackupMyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';
Uwe Keim
39.3k56 gold badges174 silver badges291 bronze badges
asked Oct 18, 2010 at 14:40
6
Yeah I just scored this one.
Look in Windows Services. Start > Administration > Services
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).
This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)
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).
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$.
Luke Girvin
13.2k9 gold badges63 silver badges84 bronze badges
answered Oct 24, 2011 at 2:18
Robin VesseyRobin Vessey
4,2791 gold badge23 silver badges21 bronze badges
11
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
answered Mar 21, 2012 at 17:34
AbuleenAbuleen
4434 silver badges8 bronze badges
0
In order to find out which user you need to give permission to do the restore process, you can follow the following steps:
You need to go to your server where SQL Server is installed. Find SQL Server Configuration Manager
Next, you need to go to «SQL Server Services»
Under your SQL Server (MSSQLSERVER) instance there will be an account with column «Logon As», in my case it is NT ServiceMSSQLSERVER.
That is the account which you need to add under Security tab of your source .bak location and give that user the «Read» permissions so that the backup file can be read.
Let’s say your backup file is present at «D:Shared» folder, then you need to give permissions like this:
answered Oct 6, 2018 at 4:48
RaghavRaghav
8,5946 gold badges80 silver badges105 bronze badges
2
One of the reason why this happens is you are running your MSSQLSERVER Service not using a local system. To fix this issue, use the following steps.
- Open run using Windows + R
- Type services.msc and a services dialog will open
- Find SQL Server (MSSQLSERVER)
- Right click and click on properties.
- Go to Log on tab
- Select Local System account and click on «Apply» and «OK»
- Click on Stop link on the left panel by selecting the «SQL Server (MSSQLSERVER)» and Start it again once completely stopped.
- Enjoy your backup.
Hope it helps you well, as it did to me. Cheers!
answered Jul 3, 2019 at 6:43
Gaurav AmatyaGaurav Amatya
3771 gold badge4 silver badges7 bronze badges
0
The SQL Server service account does not have permissions to write to the folder C:UsersKimpoyDesktopBackup
answered Oct 18, 2010 at 15:04
Martin SmithMartin Smith
435k87 gold badges737 silver badges836 bronze badges
1
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.
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.
answered Oct 3, 2012 at 2:47
JasonJason
811 silver badge3 bronze badges
I face the same problem with SQL Express 2014 SP1 on Windows 10.
Solution which work
- Open Service by typing Services
- Locate and open the 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.
- Problem solved
answered Jul 26, 2016 at 19:33
Sawarkar vikasSawarkar vikas
3151 gold badge5 silver badges9 bronze badges
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).
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.
hjavaher
2,5893 gold badges30 silver badges52 bronze badges
answered Apr 2, 2014 at 18:13
2
In my case, I forgot to name the backup file and it kept giving me the same permission error :/
TO DISK N'{path}FILENAME.bak'
answered Feb 3, 2020 at 10:58
1
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.
answered Apr 19, 2011 at 14:31
Eric BernierEric Bernier
4591 gold badge7 silver badges17 bronze badges
1
Here is what I did to by-pass the issue.
1) Go to backup
2) Remove the destination file-path to disk
3) Click on Add
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
C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupYourdb.bak
5) Click on OK
Hope this helps!
answered Aug 29, 2016 at 6:45
dpen82dpen82
2341 gold badge2 silver badges13 bronze badges
I solved the same problem with the following 3 steps:
- I store my backup file in other folder path that’s worked right.
- View different of security tab two folders (as below image).
- Edit permission in security tab folder that’s not worked right.
answered Jul 24, 2020 at 13:06
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';
answered Jul 7, 2016 at 11:42
Cem MutluCem Mutlu
1,9691 gold badge24 silver badges24 bronze badges
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).I went to the local folder (C:UsersMeDesktopBackup) and added «NT
ServiceMSSQL$SQLEXPRESS» as the user to give Write permissions.
answered Dec 12, 2017 at 13:32
Hiren ParghiHiren Parghi
1,7951 gold badge20 silver badges30 bronze badges
SQL Server is not able to access (write) the backup into the location specified.
First you need to verify the service account on which the Sql server is running. This can be done by using Configuration manager or Services.msc.
or
Use below query :
SELECT DSS.servicename, DSS.startup_type_desc, DSS.status_desc, DSS.last_startup_time, DSS.service_account, DSS.is_clustered, DSS.cluster_nodename, DSS.filename, DSS.startup_type, DSS.status, DSS.process_id FROM sys.dm_server_services AS DSS;
Now look at the column service_account and note it down.
Go to the location where you are trying to take the backup.In your case : C:UsersMeDesktopBackup
Right click—> Properties —> Security —>
Add the service account and provide read/write permissions. This will resolve the issue.
answered Aug 28, 2019 at 13:13
answered Mar 28, 2013 at 12:09
0
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:BackupAdventure_20120720_1024AM.trn’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
I verified backup folder on C drive, Is new service account is having full control access permission or not?, I realized that «TestKiran» service account is not having Full control security permission.
Please follow the below steps to give full control to service account:
- Go to C drive, Right click on Backup folder.
- Select Security tab.
- Click on Edit button, new window will open.
- Click on Add button and enter TestKiran 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.
- Select you entered user name and select Full Control check box under allow.
Brad Larson♦
170k45 gold badges396 silver badges571 bronze badges
answered Jun 6, 2013 at 9:35
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.
answered Aug 21, 2013 at 12:23
Share this folder and use UNC path, by example: pcbackupsmydb.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)
answered Sep 24, 2016 at 9:16
Gennady GGennady G
9661 gold badge11 silver badges28 bronze badges
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.
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.
answered Oct 25, 2016 at 20:05
Eric J.Eric J.
147k63 gold badges339 silver badges552 bronze badges
Hi you need to change the query from:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:UsersMeDesktopBackupMyDB.Bak'
to
BACKUP DATABASE AcinsoftDB
TO DISK = N'C:UsersMeDesktopBackupMyDB.Bak'
You have to add a N in front of the path works for me.
answered May 8, 2017 at 2:42
My issue was that the «File Ownership» was set to my company. I changed it to «Personal» and it worked. Right click the file and click the «File Ownership >» option and then change it to «Personal». I believe this happens with all files sent over Microsoft Teams.
answered Jan 26, 2021 at 17:46
RyanRyan
5245 silver badges14 bronze badges
If the backup destination path resides on your local machine, change the account of ‘SQL Server’ service to ‘Local System Account’, then everything must be resolved, keep in mind that the ‘SQL Server’ instance service is responsible to access the backup destination so the account it is running under, must have access to the destination path of your backup.
answered Mar 23, 2022 at 8:28
Make sure you are actually saving to a FILE and not a folder,
My problem was I was simply putting in the Folder path and not the File path
You want this
'F:Database BackupPharmacydata.bak';
You dont want this
'F:Database BackupPharmacy';
answered Oct 27, 2022 at 17:23
JamiscoJamisco
1,6133 gold badges13 silver badges17 bronze badges
LINUX users
Make sure the folder exists, in my case, I am taking a backup in folder SQL_bakcup
change the folder permission by executing the below command.
chmod 777 SQL_bakcup
Now run the below query, make sure to put the correct path
USE [master];
BACKUP DATABASE [master]
TO DISK = 'PATH/SQL_bakcup/SQL_LOCAL_DB.bak'
WITH NOFORMAT, NOINIT,
NAME = 'SQL_LOCAL_DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
answered May 15 at 11:58
VarunVarun
4,32219 gold badges84 silver badges118 bronze badges
05.09.16 — 09:22
Здравствуйте!
Стоит MS SQL Server 2012. Всё работало нормально, но в один день пропала возможность копировать базу и создавать резервные копии. При попытке создать резервную копию базы выдает ошибку:
Действие резервное копирование завершилось неудачно для объекта «Сервер» «SERVER».
(Microsoft.SqlServer.SmoExtended)
Дополнительные сведения:
System.Data.SqlClient.SqlError: Невозможно открыть устройство резервного копирования
‘D:Backup SQLtradedb_backup.bak’. Ошибка операционной системы 5 (Отказано в доступе.).
(Microsoft.SqlServer.Smo)
Если попытаться сделать резервную копию в другую папку — то же самое. И так же не дает просто скопировать файлы базы данных из папки, где хранятся базы SQL. Подскажите, с чем может быть связана эта проблема и как её решить?
1 — 05.09.16 — 09:25
Отказано в доступе
2 — 05.09.16 — 09:26
Посмотри, кто владелец папки и какие на неё права
3 — 05.09.16 — 09:28
(2) У всех полный доступ к папке
4 — 05.09.16 — 09:38
(0) При запущенном сервере SQL базы не копируются. Останови службу сервера (и сервер 1С Предприятие?), потом пытайся копировать
(3) Путь и права на папку проверь именно у пользователя под которым стартован SQL, по умолчанию сервер стартует с локальным пользователем, а не доменным.
5 — 05.09.16 — 09:41
(4) спасибо. Дело в том, что раньше всё копировалось и при запущенном SQL-сервере в том числе.
Права ни на папки, ни у пользователей никто не менял. Это просто произошло в один день
Хорошо, я попробую отключить службу SQL сервера и скопировать.
6 — 05.09.16 — 09:46
(4) хотя при копировании да, вы правы. Но вот резервное копирование всё-равно выполняться не хочет и при выключенном SQL сервере скопировать базу не дает.
7 — 05.09.16 — 10:15
(6) истёк срок действия пароля пользователя? выкинули из группы локальных админов?
Просто перезагрузи сервер, сразу выяснится вся информация о пользователе SQL (или всё станет на свои места, если был обрыв связи с доменом).
8 — 05.09.16 — 10:22
(7) нет, пароль неограничен, из локальной группы не выкидывали и сервер уже перезагружал)
9 — 05.09.16 — 10:28
(7) а это может быть связано с появлением еще одного сервера в сети одной рабочей группы? на нем тоже стоит SQL и пользователь Администратор, только с другим паролем. или это тут вообще не при чем?
10 — 05.09.16 — 10:30
(9) по идее не должно влиять, но, на всякий случай, раздели имена пользователей, хотя-бы Админ1 и Админ2
11 — 05.09.16 — 10:48
Засунь этого пользователя, под которым стартует скл, в группу локальных администраторов и этой группе напрямую дай права в свою папку. Получится ль?
12 — 05.09.16 — 10:55
(11) Пользователь, под которым стартует SQL — Администратор — Встроенная учетная запись сервера.
13 — 05.09.16 — 10:56
А ты залогинься под ним, и посмотри — можешь в эту папку писать хоть что-то или нет?
14 — 05.09.16 — 10:57
(13) под ним и залогинен. да могу.
15 — 05.09.16 — 11:01
хм. а попробуй новую базу сделать, просто пустую, и сделать ее резервную копию. Будет ли работать такое?
16 — 05.09.16 — 11:40
(15) нет, с новой не получилось. на любую базу так выдает. думаю, может с СКЛ-сервером что-то
17 — 05.09.16 — 11:51
Сделай нового пользователя, локального администратора, запусти скл-сервер под ним, и попробуй тоже самое.
18 — 05.09.16 — 12:27
(17) увы нет, не сработало. Ладно, буду думать и пытаться. Спасибо!!!
19 — 05.09.16 — 12:33
Рестарт самого скуля хоть делал ?
20 — 05.09.16 — 12:54
(19) конечно)
21 — 05.09.16 — 13:13
Посмотри под кем работает служба скуля. Дай этому пользователю доступ на папку или включи его в группу админов.
22 — 05.09.16 — 14:11
(21) служба работает от MSSQLSERVER, но такого пользователя нет в списке пользователей сервера
23 — 05.09.16 — 14:21
(22) Надо ручками писать это имя
24 — 05.09.16 — 14:42
25 — 05.09.16 — 14:51
(24) спасибо! это нужно прописать в доступе к папке с базами SQL? прописал, ничего не вышло. и к серверу SQL соединяюсь под пользователем Администратор, когда пытаюсь резервную копию сделать
26 — 05.09.16 — 14:56
(25) Прописать доступ к папкам с бэкапами
ac13
27 — 05.09.16 — 15:06
(26) вы представляете! получилось! спасибо большое!
I’m trying to execute the following script in SQL Server Management Studio:
USE [master]
GO
CREATE DATABASE [test1] ON PRIMARY (
NAME = N'test1',
FILENAME =
N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATAtest1.mdf',
SIZE = 70656KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON (
NAME = N'test1_log',
FILENAME =
N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATAtest1_log.ldf',
SIZE = 164672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
But I’m getting the error:
Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file
‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATAtest1.mdf’.Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Already have all role permissions for my user, any ideas on what’s wrong?
- Remove From My Forums
Присоединение базы данных
-
Вопрос
-
Вообщем, совершил глупость. Вместо отсоединения базы данных, удалил ее. Вернее, не ту удалил. Была копия базы, сделанная простым копированием. Так вот ее присоединить не удалось. Пишет «ошибка операционной системы 5:»(Отказано в доступе»). (Microsoft Sql
Server, ошибка 5120). У меня WINDOWS 7 Как выйти из ситуации?
Ответы
-
Попробуйте снять с файла все разрешения (ну или дать учетной записи, под которой запущен MS SQL разрешения на оба файла), также снимите все атрибуты, а вот потом попробуйте подключить.
Ну а если не поможет, то да, backup и restore.
-
Помечено в качестве ответа
12 июля 2012 г. 17:26
-
Помечено в качестве ответа
- Remove From My Forums
-
Вопрос
-
SQL Server 2012 Standard . Windows server 2012 R2 standard. Сервер не в домене. При попытке создать объект RackStation для бэкапа в Server Management StudioSystem получаем следующее сообщение:
Data.SqlClient.SqlError: Невозможно открыть устройство резервного копирования ‘RackStation(ссылка на раcшаренную папку на сервере NAS)’. Ошибка операционной системы 5(Отказано в доступе.). (Microsoft.SqlServer.Smo)
SQL запускается под учетной записью NT serviceMSSQLSERVER. Доступ к общей папке на NAS Synology нормально осуществляется от учетных записей сервера для обычных пользователей (естественно прописанных в NAS) . Как обеспечить разрешение
для доступа в данном случае. Возможно ли это не в доменной структуре?
Ответы
-
Попробуйте использовать
Credentials Manager, указав в нем учетные данные для подключения к NAS
Innovation distinguishes between a leader and a follower — Steve Jobs
-
Предложено в качестве ответа
20 июня 2014 г. 8:00
-
Помечено в качестве ответа
Иван ПродановMicrosoft contingent staff, Moderator
27 июня 2014 г. 5:22
-
Предложено в качестве ответа
ac13
05.09.16 — 09:22
Здравствуйте!
Стоит MS SQL Server 2012. Всё работало нормально, но в один день пропала возможность копировать базу и создавать резервные копии. При попытке создать резервную копию базы выдает ошибку:
Действие резервное копирование завершилось неудачно для объекта «Сервер» «SERVER».
(Microsoft.SqlServer.SmoExtended)
Дополнительные сведения:
System.Data.SqlClient.SqlError: Невозможно открыть устройство резервного копирования
‘D:Backup SQLtradedb_backup.bak’. Ошибка операционной системы 5 (Отказано в доступе.).
(Microsoft.SqlServer.Smo)
Если попытаться сделать резервную копию в другую папку — то же самое. И так же не дает просто скопировать файлы базы данных из папки, где хранятся базы SQL. Подскажите, с чем может быть связана эта проблема и как её решить?
Ёпрст
1 — 05.09.16 — 09:25
Отказано в доступе
Ёпрст
2 — 05.09.16 — 09:26
Посмотри, кто владелец папки и какие на неё права
ac13
3 — 05.09.16 — 09:28
(2) У всех полный доступ к папке
Fedor-1971
4 — 05.09.16 — 09:38
(0) При запущенном сервере SQL базы не копируются. Останови службу сервера (и сервер 1С Предприятие?), потом пытайся копировать
(3) Путь и права на папку проверь именно у пользователя под которым стартован SQL, по умолчанию сервер стартует с локальным пользователем, а не доменным.
ac13
5 — 05.09.16 — 09:41
(4) спасибо. Дело в том, что раньше всё копировалось и при запущенном SQL-сервере в том числе.
Права ни на папки, ни у пользователей никто не менял. Это просто произошло в один день
Хорошо, я попробую отключить службу SQL сервера и скопировать.
ac13
6 — 05.09.16 — 09:46
(4) хотя при копировании да, вы правы. Но вот резервное копирование всё-равно выполняться не хочет и при выключенном SQL сервере скопировать базу не дает.
Fedor-1971
7 — 05.09.16 — 10:15
(6) истёк срок действия пароля пользователя? выкинули из группы локальных админов?
Просто перезагрузи сервер, сразу выяснится вся информация о пользователе SQL (или всё станет на свои места, если был обрыв связи с доменом).
ac13
8 — 05.09.16 — 10:22
(7) нет, пароль неограничен, из локальной группы не выкидывали и сервер уже перезагружал)
ac13
9 — 05.09.16 — 10:28
(7) а это может быть связано с появлением еще одного сервера в сети одной рабочей группы? на нем тоже стоит SQL и пользователь Администратор, только с другим паролем. или это тут вообще не при чем?
Fedor-1971
10 — 05.09.16 — 10:30
(9) по идее не должно влиять, но, на всякий случай, раздели имена пользователей, хотя-бы Админ1 и Админ2
Новиков
11 — 05.09.16 — 10:48
Засунь этого пользователя, под которым стартует скл, в группу локальных администраторов и этой группе напрямую дай права в свою папку. Получится ль?
ac13
12 — 05.09.16 — 10:55
(11) Пользователь, под которым стартует SQL — Администратор — Встроенная учетная запись сервера.
Новиков
13 — 05.09.16 — 10:56
А ты залогинься под ним, и посмотри — можешь в эту папку писать хоть что-то или нет?
ac13
14 — 05.09.16 — 10:57
(13) под ним и залогинен. да могу.
Новиков
15 — 05.09.16 — 11:01
хм. а попробуй новую базу сделать, просто пустую, и сделать ее резервную копию. Будет ли работать такое?
ac13
16 — 05.09.16 — 11:40
(15) нет, с новой не получилось. на любую базу так выдает. думаю, может с СКЛ-сервером что-то
Новиков
17 — 05.09.16 — 11:51
Сделай нового пользователя, локального администратора, запусти скл-сервер под ним, и попробуй тоже самое.
ac13
18 — 05.09.16 — 12:27
(17) увы нет, не сработало. Ладно, буду думать и пытаться. Спасибо!!!
Ёпрст
19 — 05.09.16 — 12:33
Рестарт самого скуля хоть делал ?
ac13
20 — 05.09.16 — 12:54
(19) конечно)
leonidkorolev
21 — 05.09.16 — 13:13
Посмотри под кем работает служба скуля. Дай этому пользователю доступ на папку или включи его в группу админов.
ac13
22 — 05.09.16 — 14:11
(21) служба работает от MSSQLSERVER, но такого пользователя нет в списке пользователей сервера
leonidkorolev
23 — 05.09.16 — 14:21
(22) Надо ручками писать это имя
leonidkorolev
24 — 05.09.16 — 14:42
ac13
25 — 05.09.16 — 14:51
(24) спасибо! это нужно прописать в доступе к папке с базами SQL? прописал, ничего не вышло. и к серверу SQL соединяюсь под пользователем Администратор, когда пытаюсь резервную копию сделать
leonidkorolev
26 — 05.09.16 — 14:56
(25) Прописать доступ к папкам с бэкапами
ac13
27 — 05.09.16 — 15:06
(26) вы представляете! получилось! спасибо большое!
Вопрос
Ниже приведен запрос, который я использую для резервного копирования (создания файла .bak
) моей базы данных.
Однако, когда бы я его ни запускал, я всегда получаю следующее сообщение об ошибке:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:UsersMeDesktopBackupMyDB.Bak’. Ошибка операционной системы 5(Доступ запрещен.).Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE завершается с ошибкой.
Вот мой запрос:
Заранее спасибо.
Решение / Ответ
24-го октября 2011 в 2:18
2011-10-24T02:18:00+00:00
#11199156
Да, я только что забил его.
Загляните в Службы Windows. Пуск > Администрирование > Службы
Найдите в списке службу под названием: SQL Server (MSSQLSERVER) найдите колонку «Log On As» (нужно добавить ее, если ее нет в списке).
Это учетная запись, которой нужно дать разрешения на каталог, щелкните правой кнопкой мыши в проводнике > свойства > Общие ресурсы (и безопасность).
ПРИМЕЧАНИЕ: Не забудьте дать права на фактический каталог и на общий ресурс, если вы работаете по сети.
Примените и подождите, пока разрешения не будут установлены, и повторите попытку резервного копирования.
ПРИМЕЧАНИЕ 2: если вы выполняете резервное копирование по сети, а ваш SQL работает как «Локальная служба», то у вас проблемы… Вы можете попробовать назначить разрешения или проще сделать резервную копию локально и скопировать ее за пределы SQL Server (через час).
ПРИМЕЧАНИЕ 3: Если вы работаете как сетевая служба, то в некоторых случаях удаленная машина не распознает сетевую службу на вашем SQL Server. В этом случае вам нужно добавить разрешения для самого компьютера, например, MyServer$.
Ответ на вопрос
21-го марта 2012 в 5:34
2012-03-21T17:34:13+00:00
#11199157
Перейдите в папку SQL Server в меню Пуск и выберите команду Инструменты Настройки
Выберите Диспетчер конфигурации SQL сервера
В службы SQL Server на нужные инстанции изменить (войдите в систему) для локальной системы
Ответ на вопрос
18-го октября 2010 в 3:04
2010-10-18T15:04:06+00:00
#11199154
Учетная запись службы SQL Server не имеет прав на запись в папку C:UsersKimpoyDesktopBackup
.
Ответ на вопрос
3-го октября 2012 в 2:47
2012-10-03T02:47:26+00:00
#11199158
Я имел эту проблему недавно, однако я был запуск задания резервного копирования с сервера, но база данных резервного копирования на сервере в общей папке на сервере С. Когда агент на сервер говорит сервер B, чтобы выполнить резервное копирование, Т-SQL команды, его на самом деле обслуживание счета, что SQL выполняется на сервере B, который пытается записать резервную копию на сервере С.
Просто помните, что его учетная запись службы SQL-сервера, выполнив команду резервное копирование базы данных-это то, что должен иметь права на файловой системе, а не агент.
Ответ на вопрос
2-го апреля 2014 в 6:13
2014-04-02T18:13:23+00:00
#11199162
Я был просто переживает это сам. Я заверил, что мой логин был именно mssqlserver полный доступ
, но это было все еще вызывает вопросы. Он только один раз работал я переехала назначения в корень С. Еще более важно из папки пользователя (хотя у меня была поделиться со всеми разрешениями — даже пытался, что «Все» в качестве теста).
Я не’т знать, если я считаю, что мой вопрос на «фиксированный» и, однако, он является»рабочее"&;.
Просто к сведению для других пользователей, которые приходят через этот поток.
Ответ на вопрос
26-го июля 2016 в 7:33
2016-07-26T19:33:00+00:00
#11199164
Я сталкиваюсь с той же проблемой с SQL Экспресс 2014 SP1 на Windows 10.
Решение, которое работает
- Откройте службу, введя услуг
- Найдите и откройте SQL сервер (отчеты)
- Перейдите на вкладку Вход в систему
- Выберите локальную системную учетную запись ( также проверить услуг для разрешить взаимодействие с рабочим столом )
- Нажмите кнопку ОК . Остановить службу . Перезапустите службу.
- Проблема решена
Ответ на вопрос
6-го октября 2018 в 4:48
2018-10-06T04:48:28+00:00
#11199170
Для того, чтобы узнать, какой пользователь вы должны дать разрешение, чтобы сделать процесс восстановления, вы можете выполнить следующие действия:
Вам нужно идти к вашему сервере, где установлен SQL-сервер. Найти диспетчер конфигурации SQL-сервера
Далее, вам нужно идти, чтобы «SQL сервер служб и»
Под SQL-сервер (служба mssqlserver) экземпляр появится аккаунт с колонкой на «входа», в моем случае это служба NTимя mssqlserver.
Это счет, который нужно добавить на вкладке Безопасность вашего источника .бак местоположения и дать этому пользователю в «Читать» и разрешения, так что файл резервной копии можно прочитать.
Позвольте’ы сказать ваш файл резервного копирования в «D:Shared» папка, то нужно дать подобного разрешения:
Ответ на вопрос
19-го апреля 2011 в 2:31
2011-04-19T14:31:25+00:00
#11199155
У меня была похожая проблема. Я добавил разрешения на запись в сам файл .bak и в папку, в которую я записывал резервную копию, для пользователя NETWORK SERVICE. Чтобы добавить разрешения, просто щелкните правой кнопкой мыши файл/каталог, который вы хотите изменить, выберите вкладку безопасности и добавьте туда соответствующих пользователей/разрешения.
Ответ на вопрос
29-го августа 2016 в 6:45
2016-08-29T06:45:10+00:00
#11199165
Вот что я сделал, чтобы обойти эту проблему.
-
Заходим в резервное копирование
-
удалить файл-путь на диск
-
Нажмите на кнопку Добавить
-
в имени файла: флажок вручную ввести имя резервной копии, после резервной копии ..как ниже, где Yourdb.BAK-имя резервной копии базы данных
C:Program файлыMSSQL11 Майкрософт SQL сервер.Имя mssqlserverMSSQL данныхрезервное копированиеYourdb.бак
- Нажмите кнопку ОК
Надеюсь, что это помогает!
Ответ на вопрос
7-го июля 2016 в 11:42
2016-07-07T11:42:44+00:00
#11199163
Я знаю, что это не точное решение, но с использованием внешних дорожек диска решает эту проблему.
Ответ на вопрос
21-го августа 2013 в 12:23
2013-08-21T12:23:44+00:00
#11199161
Пожалуйста, проверьте доступ к дискам.Сначала создайте одну папку и заходим в Свойства папки ,
Вы можете найти вкладку Безопасность ,нажмите на эту проверить, есть ли ваш ID пользователя, имеющего доступ или нет.
если бы’т найти ваш ID,пожалуйста, нажмите на Добавить кнопок и дать имя пользователя с полным доступом.
Ответ на вопрос
24-го сентября 2016 в 9:16
2016-09-24T09:16:14+00:00
#11199166
Общий доступ к этой папке и использовать UNC-путь, например: ПКрезервное копированиебазы данных mydb.бак
Тогда вы можете прекратить акцию.
Не очень элегантный, но он решает все проблемы с разрешениями (вы должны дать разрешения на акцию, а также, Как упоминалось выше)
Ответ на вопрос
25-го октября 2016 в 8:05
2016-10-25T20:05:48+00:00
#11199167
Я сталкивалась с этой проблемой, когда .Файл BAK был временно хранятся в папке зашифрованном с помощью BitLocker. Он сохранил шифрования после того, как он был перемещен в другую папку.
Учетная запись сетевой службы не удалось расшифровать файл и дал тщательно информативное сообщение об ошибке.
Снятие шифрования BitLocker (исключая «и шифровать содержимое для защиты данных» в свойствах файла) на .Файл BAK решен вопрос.
Ответ на вопрос
8-го мая 2017 в 2:42
2017-05-08T02:42:46+00:00
#11199168
Привет вам нужно изменить запрос:
для
Вы должны добавить N в перед путь работает для меня.
Ответ на вопрос
12-го декабря 2017 в 1:32
2017-12-12T13:32:48+00:00
#11199169
У меня такая же ошибка. Следующие изменения помогли мне это исправить.
Я должен был проверить диспетчер сервера-и gt;Инструменты->Услуги и найти пользователя (на»Журнал
О как» столбик) для обслуживания следующих объектов: SQL-сервер (отчеты).Я пошел в локальную папку (C:UsersMeDesktopBackup) и добавил, что «НТ
отчеты&яслужба MSSQL$; как пользователю дать права на запись.
Ответ на вопрос
6-го июня 2013 в 9:35
2013-06-06T09:35:30+00:00
#11199160
Сообщение 3201, Уровень 16, Состояние 1, Строка 1
Невозможно открыть устройство резервного копирования ‘C:BackupAdventure_20120720_1024AM.trn’. Ошибка операционной системы 5(отказано в доступе).
Сообщение 3013, Уровень 16, Состояние 1, Строка 1
Журнал резервного копирования завершается аварийно.
Я проверил папку резервного копирования на диск C, это новая учетная запись службы имеет полный доступ разрешение на доступ или нет?, Я понял, что «Тест Киран» и учетная запись службы не имея полного доступа безопасности.
Пожалуйста, следуйте ниже шагов, чтобы дать полный контроль для учетной записи службы:
- Перейти на диск C, щелкните правой кнопкой мыши на папке Backup.
- Выберите вкладку Безопасность.
- Нажмите на кнопку Редактировать, откроется новое окно.
- Нажмите на кнопку Добавить и введите тестовую учетную запись пользователяКиран и нажмите кнопку Проверить имя, это будет проверять введенные пользователем или нет, если он существует, он будет показывать пользователю окно, выберите ОК.
- Выберите вы ввели имя пользователя и выберите полный доступ установите флажок Разрешить.
Ответ на вопрос
3-го июля 2019 в 6:43
2019-07-03T06:43:24+00:00
#11199171
Одна из причин, почему это происходит, вы бежите ваша служба mssqlserver не используя локальную систему. Чтобы устранить эту проблему, выполните следующие действия.
- Откройте окно Выполнить с помощью Windows + Р
- Тип услуг.МСЦ и диалоговое окно Services откроет
- Найти SQL сервер (служба mssqlserver)
- Щелкните правой кнопкой мыши и выберите пункт «Свойства».
- Перейдите в журнал на вкладке
- Выберите локальную системную учетную запись и нажмите на «применить» и «ОК»и
- Нажмите на ссылку остановить на панели слева, выбрав «и SQL-сервер (служба mssqlserver)» и запустить его снова, как только полностью остановлен.
- Наслаждайтесь вашей резервной копии.
Надеемся это поможет вам хорошо, как это сделали со мной. Ура!
Ответ на вопрос
28-го августа 2019 в 1:13
2019-08-28T13:13:16+00:00
#11199172
SQL сервер не может получить доступ (писать) резервное копирование в указанное место.
Сначала нужно проверить учетную запись службы, на котором SQL-сервер работает. Это можно сделать с помощью Configuration Manager или услуг.МСЦ.
или
Использовать ниже запроса :
выберите ДСС.имя_службы,
ДСС.startup_type_desc,
ДСС.status_desc,
ДСС.last_startup_time,
ДСС.service_account,
ДСС.is_clustered,
ДСС.cluster_nodename,
ДСС.именем,
ДСС.startup_type,
ДСС.статус
ДСС.идентификатор_процесса из sys.dm_server_services как ДСС;
Теперь посмотрим на service_account столбца и запишите это.
Перейдите к расположению, где вы пытаетесь взять резервную копию.В вашем случае : C:UsersMeDesktopBackup
Щелкните правой кнопкой мыши—> Свойства —> безопасность-и GT;
Добавьте учетную запись службы и обеспечивают чтение/запись. Это позволит решить проблему.
Below is the query that I am using to backup (create a .bak
) my database.
However, whenever I run it, I always get this error message:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:UsersMeDesktopBackupMyDB.Bak’. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
This is my query:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:UsersMeDesktopBackupMyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';
Uwe Keim
39k56 gold badges174 silver badges289 bronze badges
asked Oct 18, 2010 at 14:40
5
Yeah I just scored this one.
Look in Windows Services. Start > Administration > Services
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).
This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)
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).
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$.
Luke Girvin
13.1k8 gold badges62 silver badges83 bronze badges
answered Oct 24, 2011 at 2:18
Robin VesseyRobin Vessey
4,1891 gold badge22 silver badges21 bronze badges
11
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
answered Mar 21, 2012 at 17:34
AbuleenAbuleen
4134 silver badges8 bronze badges
0
In order to find out which user you need to give permission to do the restore process, you can follow the following steps:
You need to go to your server where SQL Server is installed. Find SQL Server Configuration Manager
Next, you need to go to «SQL Server Services»
Under your SQL Server (MSSQLSERVER) instance there will be an account with column «Logon As», in my case it is NT ServiceMSSQLSERVER.
That is the account which you need to add under Security tab of your source .bak location and give that user the «Read» permissions so that the backup file can be read.
Let’s say your backup file is present at «D:Shared» folder, then you need to give permissions like this:
answered Oct 6, 2018 at 4:48
RaghavRaghav
8,1866 gold badges78 silver badges104 bronze badges
2
One of the reason why this happens is you are running your MSSQLSERVER Service not using a local system. To fix this issue, use the following steps.
- Open run using Windows + R
- Type services.msc and a services dialog will open
- Find SQL Server (MSSQLSERVER)
- Right click and click on properties.
- Go to Log on tab
- Select Local System account and click on «Apply» and «OK»
- Click on Stop link on the left panel by selecting the «SQL Server (MSSQLSERVER)» and Start it again once completely stopped.
- Enjoy your backup.
Hope it helps you well, as it did to me. Cheers!
answered Jul 3, 2019 at 6:43
Gaurav AmatyaGaurav Amatya
3571 gold badge4 silver badges7 bronze badges
0
The SQL Server service account does not have permissions to write to the folder C:UsersKimpoyDesktopBackup
answered Oct 18, 2010 at 15:04
Martin SmithMartin Smith
429k87 gold badges725 silver badges824 bronze badges
1
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.
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.
answered Oct 3, 2012 at 2:47
JasonJason
811 silver badge3 bronze badges
I face the same problem with SQL Express 2014 SP1 on Windows 10.
Solution which work
- Open Service by typing Services
- Locate and open the 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.
- Problem solved
answered Jul 26, 2016 at 19:33
Sawarkar vikasSawarkar vikas
3151 gold badge5 silver badges9 bronze badges
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).
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.
hjavaher
2,5693 gold badges29 silver badges52 bronze badges
answered Apr 2, 2014 at 18:13
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.
answered Apr 19, 2011 at 14:31
Eric BernierEric Bernier
4591 gold badge8 silver badges17 bronze badges
1
Here is what I did to by-pass the issue.
1) Go to backup
2) Remove the destination file-path to disk
3) Click on Add
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
C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupYourdb.bak
5) Click on OK
Hope this helps!
answered Aug 29, 2016 at 6:45
dpen82dpen82
2241 gold badge2 silver badges13 bronze badges
I solved the same problem with the following 3 steps:
- I store my backup file in other folder path that’s worked right.
- View different of security tab two folders (as below image).
- Edit permission in security tab folder that’s not worked right.
answered Jul 24, 2020 at 13:06
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';
answered Jul 7, 2016 at 11:42
Cem MutluCem Mutlu
1,8991 gold badge25 silver badges24 bronze badges
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).I went to the local folder (C:UsersMeDesktopBackup) and added «NT
ServiceMSSQL$SQLEXPRESS» as the user to give Write permissions.
answered Dec 12, 2017 at 13:32
Hiren ParghiHiren Parghi
1,7531 gold badge19 silver badges29 bronze badges
SQL Server is not able to access (write) the backup into the location specified.
First you need to verify the service account on which the Sql server is running. This can be done by using Configuration manager or Services.msc.
or
Use below query :
SELECT DSS.servicename, DSS.startup_type_desc, DSS.status_desc, DSS.last_startup_time, DSS.service_account, DSS.is_clustered, DSS.cluster_nodename, DSS.filename, DSS.startup_type, DSS.status, DSS.process_id FROM sys.dm_server_services AS DSS;
Now look at the column service_account and note it down.
Go to the location where you are trying to take the backup.In your case : C:UsersMeDesktopBackup
Right click—> Properties —> Security —>
Add the service account and provide read/write permissions. This will resolve the issue.
answered Aug 28, 2019 at 13:13
In my case, I forgot to name the backup file and it kept giving me the same permission error :/
TO DISK N'{path}WRITE_YOUR_BACKUP_FILENAME_HERE.bak'
answered Feb 3, 2020 at 10:58
answered Mar 28, 2013 at 12:09
0
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:BackupAdventure_20120720_1024AM.trn’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
I verified backup folder on C drive, Is new service account is having full control access permission or not?, I realized that «TestKiran» service account is not having Full control security permission.
Please follow the below steps to give full control to service account:
- Go to C drive, Right click on Backup folder.
- Select Security tab.
- Click on Edit button, new window will open.
- Click on Add button and enter TestKiran 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.
- Select you entered user name and select Full Control check box under allow.
Brad Larson♦
170k45 gold badges397 silver badges571 bronze badges
answered Jun 6, 2013 at 9:35
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.
answered Aug 21, 2013 at 12:23
Share this folder and use UNC path, by example: pcbackupsmydb.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)
answered Sep 24, 2016 at 9:16
Gennady GGennady G
9361 gold badge11 silver badges28 bronze badges
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.
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.
answered Oct 25, 2016 at 20:05
Eric J.Eric J.
146k63 gold badges336 silver badges549 bronze badges
Hi you need to change the query from:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:UsersMeDesktopBackupMyDB.Bak'
to
BACKUP DATABASE AcinsoftDB
TO DISK = N'C:UsersMeDesktopBackupMyDB.Bak'
You have to add a N in front of the path works for me.
answered May 8, 2017 at 2:42
My issue was that the «File Ownership» was set to my company. I changed it to «Personal» and it worked. Right click the file and click the «File Ownership >» option and then change it to «Personal». I believe this happens with all files sent over Microsoft Teams.
answered Jan 26, 2021 at 17:46
RyanRyan
5245 silver badges14 bronze badges
If the backup destination path resides on your local machine, change the account of ‘SQL Server’ service to ‘Local System Account’, then everything must be resolved, keep in mind that the ‘SQL Server’ instance service is responsible to access the backup destination so the account it is running under, must have access to the destination path of your backup.
answered Mar 23, 2022 at 8:28
Make sure you are actually saving to a FILE and not a folder,
My problem was I was simply putting in the Folder path and not the File path
You want this
'F:Database BackupPharmacydata.bak';
You dont want this
'F:Database BackupPharmacy';
answered Oct 27, 2022 at 17:23
JamiscoJamisco
1,5833 gold badges13 silver badges17 bronze badges
31 / 31 / 4 Регистрация: 15.11.2008 Сообщений: 205 |
|
1 |
|
Ошибка при попытке сделать резервную копию28.11.2008, 10:08. Показов 34309. Ответов 4
Жму на базу — задачи — создать резервную копию. Вылезает менюшка, выбираю дирикторию, пишу имя файла , например 1.bak, нажимаю ок и вылазит ошибка:
__________________ 0 |
Администратор 82657 / 51230 / 244 Регистрация: 10.04.2006 Сообщений: 13,347 |
|
28.11.2008, 10:24 |
2 |
Под какой авторизацией зашел? 0 |
31 / 31 / 4 Регистрация: 15.11.2008 Сообщений: 205 |
|
28.11.2008, 10:26 [ТС] |
3 |
Все разобрался, в службе скуля поставил с системной учотной записью. Спасибо 0 |
9 / 9 / 4 Регистрация: 13.05.2011 Сообщений: 72 |
|
06.08.2015, 10:25 |
4 |
Здравствуйте, а можно по подробней о решении проблемы? А то уже всё испробовал, и никак не могу создать Backup. 0 |
3 / 3 / 0 Регистрация: 04.03.2016 Сообщений: 1 |
|
04.03.2016, 13:06 |
5 |
Здравствуйте, а можно по подробней о решении проблемы? А то уже всё испробовал, и никак не могу создать Backup. у меня была такая проблема и получилось её устранить настроив права, как у папки C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQL 3 |
Below is the query that I am using to backup (create a .bak
) my database.
However, whenever I run it, I always get this error message:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:UsersMeDesktopBackupMyDB.Bak’. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
This is my query:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:UsersMeDesktopBackupMyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';
Uwe Keim
39k56 gold badges174 silver badges289 bronze badges
asked Oct 18, 2010 at 14:40
5
Yeah I just scored this one.
Look in Windows Services. Start > Administration > Services
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).
This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)
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).
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$.
Luke Girvin
13.1k8 gold badges62 silver badges83 bronze badges
answered Oct 24, 2011 at 2:18
Robin VesseyRobin Vessey
4,1891 gold badge22 silver badges21 bronze badges
11
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
answered Mar 21, 2012 at 17:34
AbuleenAbuleen
4134 silver badges8 bronze badges
0
In order to find out which user you need to give permission to do the restore process, you can follow the following steps:
You need to go to your server where SQL Server is installed. Find SQL Server Configuration Manager
Next, you need to go to «SQL Server Services»
Under your SQL Server (MSSQLSERVER) instance there will be an account with column «Logon As», in my case it is NT ServiceMSSQLSERVER.
That is the account which you need to add under Security tab of your source .bak location and give that user the «Read» permissions so that the backup file can be read.
Let’s say your backup file is present at «D:Shared» folder, then you need to give permissions like this:
answered Oct 6, 2018 at 4:48
RaghavRaghav
8,1866 gold badges78 silver badges104 bronze badges
2
One of the reason why this happens is you are running your MSSQLSERVER Service not using a local system. To fix this issue, use the following steps.
- Open run using Windows + R
- Type services.msc and a services dialog will open
- Find SQL Server (MSSQLSERVER)
- Right click and click on properties.
- Go to Log on tab
- Select Local System account and click on «Apply» and «OK»
- Click on Stop link on the left panel by selecting the «SQL Server (MSSQLSERVER)» and Start it again once completely stopped.
- Enjoy your backup.
Hope it helps you well, as it did to me. Cheers!
answered Jul 3, 2019 at 6:43
Gaurav AmatyaGaurav Amatya
3571 gold badge4 silver badges7 bronze badges
0
The SQL Server service account does not have permissions to write to the folder C:UsersKimpoyDesktopBackup
answered Oct 18, 2010 at 15:04
Martin SmithMartin Smith
429k87 gold badges725 silver badges824 bronze badges
1
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.
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.
answered Oct 3, 2012 at 2:47
JasonJason
811 silver badge3 bronze badges
I face the same problem with SQL Express 2014 SP1 on Windows 10.
Solution which work
- Open Service by typing Services
- Locate and open the 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.
- Problem solved
answered Jul 26, 2016 at 19:33
Sawarkar vikasSawarkar vikas
3151 gold badge5 silver badges9 bronze badges
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).
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.
hjavaher
2,5693 gold badges29 silver badges52 bronze badges
answered Apr 2, 2014 at 18:13
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.
answered Apr 19, 2011 at 14:31
Eric BernierEric Bernier
4591 gold badge8 silver badges17 bronze badges
1
Here is what I did to by-pass the issue.
1) Go to backup
2) Remove the destination file-path to disk
3) Click on Add
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
C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupYourdb.bak
5) Click on OK
Hope this helps!
answered Aug 29, 2016 at 6:45
dpen82dpen82
2241 gold badge2 silver badges13 bronze badges
I solved the same problem with the following 3 steps:
- I store my backup file in other folder path that’s worked right.
- View different of security tab two folders (as below image).
- Edit permission in security tab folder that’s not worked right.
answered Jul 24, 2020 at 13:06
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';
answered Jul 7, 2016 at 11:42
Cem MutluCem Mutlu
1,8991 gold badge25 silver badges24 bronze badges
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).I went to the local folder (C:UsersMeDesktopBackup) and added «NT
ServiceMSSQL$SQLEXPRESS» as the user to give Write permissions.
answered Dec 12, 2017 at 13:32
Hiren ParghiHiren Parghi
1,7531 gold badge19 silver badges29 bronze badges
SQL Server is not able to access (write) the backup into the location specified.
First you need to verify the service account on which the Sql server is running. This can be done by using Configuration manager or Services.msc.
or
Use below query :
SELECT DSS.servicename, DSS.startup_type_desc, DSS.status_desc, DSS.last_startup_time, DSS.service_account, DSS.is_clustered, DSS.cluster_nodename, DSS.filename, DSS.startup_type, DSS.status, DSS.process_id FROM sys.dm_server_services AS DSS;
Now look at the column service_account and note it down.
Go to the location where you are trying to take the backup.In your case : C:UsersMeDesktopBackup
Right click—> Properties —> Security —>
Add the service account and provide read/write permissions. This will resolve the issue.
answered Aug 28, 2019 at 13:13
In my case, I forgot to name the backup file and it kept giving me the same permission error :/
TO DISK N'{path}WRITE_YOUR_BACKUP_FILENAME_HERE.bak'
answered Feb 3, 2020 at 10:58
answered Mar 28, 2013 at 12:09
0
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:BackupAdventure_20120720_1024AM.trn’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
I verified backup folder on C drive, Is new service account is having full control access permission or not?, I realized that «TestKiran» service account is not having Full control security permission.
Please follow the below steps to give full control to service account:
- Go to C drive, Right click on Backup folder.
- Select Security tab.
- Click on Edit button, new window will open.
- Click on Add button and enter TestKiran 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.
- Select you entered user name and select Full Control check box under allow.
Brad Larson♦
170k45 gold badges397 silver badges571 bronze badges
answered Jun 6, 2013 at 9:35
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.
answered Aug 21, 2013 at 12:23
Share this folder and use UNC path, by example: pcbackupsmydb.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)
answered Sep 24, 2016 at 9:16
Gennady GGennady G
9361 gold badge11 silver badges28 bronze badges
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.
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.
answered Oct 25, 2016 at 20:05
Eric J.Eric J.
146k63 gold badges336 silver badges549 bronze badges
Hi you need to change the query from:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:UsersMeDesktopBackupMyDB.Bak'
to
BACKUP DATABASE AcinsoftDB
TO DISK = N'C:UsersMeDesktopBackupMyDB.Bak'
You have to add a N in front of the path works for me.
answered May 8, 2017 at 2:42
My issue was that the «File Ownership» was set to my company. I changed it to «Personal» and it worked. Right click the file and click the «File Ownership >» option and then change it to «Personal». I believe this happens with all files sent over Microsoft Teams.
answered Jan 26, 2021 at 17:46
RyanRyan
5245 silver badges14 bronze badges
If the backup destination path resides on your local machine, change the account of ‘SQL Server’ service to ‘Local System Account’, then everything must be resolved, keep in mind that the ‘SQL Server’ instance service is responsible to access the backup destination so the account it is running under, must have access to the destination path of your backup.
answered Mar 23, 2022 at 8:28
Make sure you are actually saving to a FILE and not a folder,
My problem was I was simply putting in the Folder path and not the File path
You want this
'F:Database BackupPharmacydata.bak';
You dont want this
'F:Database BackupPharmacy';
answered Oct 27, 2022 at 17:23
JamiscoJamisco
1,5833 gold badges13 silver badges17 bronze badges
Below is the query that I am using to backup (create a .bak
) my database.
However, whenever I run it, I always get this error message:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:UsersMeDesktopBackupMyDB.Bak’. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
This is my query:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:UsersMeDesktopBackupMyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';
Uwe Keim
39k56 gold badges174 silver badges289 bronze badges
asked Oct 18, 2010 at 14:40
5
Yeah I just scored this one.
Look in Windows Services. Start > Administration > Services
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).
This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)
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).
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$.
Luke Girvin
13.1k8 gold badges62 silver badges83 bronze badges
answered Oct 24, 2011 at 2:18
Robin VesseyRobin Vessey
4,1891 gold badge22 silver badges21 bronze badges
11
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
answered Mar 21, 2012 at 17:34
AbuleenAbuleen
4134 silver badges8 bronze badges
0
In order to find out which user you need to give permission to do the restore process, you can follow the following steps:
You need to go to your server where SQL Server is installed. Find SQL Server Configuration Manager
Next, you need to go to «SQL Server Services»
Under your SQL Server (MSSQLSERVER) instance there will be an account with column «Logon As», in my case it is NT ServiceMSSQLSERVER.
That is the account which you need to add under Security tab of your source .bak location and give that user the «Read» permissions so that the backup file can be read.
Let’s say your backup file is present at «D:Shared» folder, then you need to give permissions like this:
answered Oct 6, 2018 at 4:48
RaghavRaghav
8,1866 gold badges78 silver badges104 bronze badges
2
One of the reason why this happens is you are running your MSSQLSERVER Service not using a local system. To fix this issue, use the following steps.
- Open run using Windows + R
- Type services.msc and a services dialog will open
- Find SQL Server (MSSQLSERVER)
- Right click and click on properties.
- Go to Log on tab
- Select Local System account and click on «Apply» and «OK»
- Click on Stop link on the left panel by selecting the «SQL Server (MSSQLSERVER)» and Start it again once completely stopped.
- Enjoy your backup.
Hope it helps you well, as it did to me. Cheers!
answered Jul 3, 2019 at 6:43
Gaurav AmatyaGaurav Amatya
3571 gold badge4 silver badges7 bronze badges
0
The SQL Server service account does not have permissions to write to the folder C:UsersKimpoyDesktopBackup
answered Oct 18, 2010 at 15:04
Martin SmithMartin Smith
429k87 gold badges725 silver badges824 bronze badges
1
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.
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.
answered Oct 3, 2012 at 2:47
JasonJason
811 silver badge3 bronze badges
I face the same problem with SQL Express 2014 SP1 on Windows 10.
Solution which work
- Open Service by typing Services
- Locate and open the 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.
- Problem solved
answered Jul 26, 2016 at 19:33
Sawarkar vikasSawarkar vikas
3151 gold badge5 silver badges9 bronze badges
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).
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.
hjavaher
2,5693 gold badges29 silver badges52 bronze badges
answered Apr 2, 2014 at 18:13
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.
answered Apr 19, 2011 at 14:31
Eric BernierEric Bernier
4591 gold badge8 silver badges17 bronze badges
1
Here is what I did to by-pass the issue.
1) Go to backup
2) Remove the destination file-path to disk
3) Click on Add
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
C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupYourdb.bak
5) Click on OK
Hope this helps!
answered Aug 29, 2016 at 6:45
dpen82dpen82
2241 gold badge2 silver badges13 bronze badges
I solved the same problem with the following 3 steps:
- I store my backup file in other folder path that’s worked right.
- View different of security tab two folders (as below image).
- Edit permission in security tab folder that’s not worked right.
answered Jul 24, 2020 at 13:06
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';
answered Jul 7, 2016 at 11:42
Cem MutluCem Mutlu
1,8991 gold badge25 silver badges24 bronze badges
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).I went to the local folder (C:UsersMeDesktopBackup) and added «NT
ServiceMSSQL$SQLEXPRESS» as the user to give Write permissions.
answered Dec 12, 2017 at 13:32
Hiren ParghiHiren Parghi
1,7531 gold badge19 silver badges29 bronze badges
SQL Server is not able to access (write) the backup into the location specified.
First you need to verify the service account on which the Sql server is running. This can be done by using Configuration manager or Services.msc.
or
Use below query :
SELECT DSS.servicename, DSS.startup_type_desc, DSS.status_desc, DSS.last_startup_time, DSS.service_account, DSS.is_clustered, DSS.cluster_nodename, DSS.filename, DSS.startup_type, DSS.status, DSS.process_id FROM sys.dm_server_services AS DSS;
Now look at the column service_account and note it down.
Go to the location where you are trying to take the backup.In your case : C:UsersMeDesktopBackup
Right click—> Properties —> Security —>
Add the service account and provide read/write permissions. This will resolve the issue.
answered Aug 28, 2019 at 13:13
In my case, I forgot to name the backup file and it kept giving me the same permission error :/
TO DISK N'{path}WRITE_YOUR_BACKUP_FILENAME_HERE.bak'
answered Feb 3, 2020 at 10:58
answered Mar 28, 2013 at 12:09
0
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:BackupAdventure_20120720_1024AM.trn’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
I verified backup folder on C drive, Is new service account is having full control access permission or not?, I realized that «TestKiran» service account is not having Full control security permission.
Please follow the below steps to give full control to service account:
- Go to C drive, Right click on Backup folder.
- Select Security tab.
- Click on Edit button, new window will open.
- Click on Add button and enter TestKiran 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.
- Select you entered user name and select Full Control check box under allow.
Brad Larson♦
170k45 gold badges397 silver badges571 bronze badges
answered Jun 6, 2013 at 9:35
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.
answered Aug 21, 2013 at 12:23
Share this folder and use UNC path, by example: pcbackupsmydb.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)
answered Sep 24, 2016 at 9:16
Gennady GGennady G
9361 gold badge11 silver badges28 bronze badges
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.
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.
answered Oct 25, 2016 at 20:05
Eric J.Eric J.
146k63 gold badges336 silver badges549 bronze badges
Hi you need to change the query from:
BACKUP DATABASE AcinsoftDB
TO DISK = 'C:UsersMeDesktopBackupMyDB.Bak'
to
BACKUP DATABASE AcinsoftDB
TO DISK = N'C:UsersMeDesktopBackupMyDB.Bak'
You have to add a N in front of the path works for me.
answered May 8, 2017 at 2:42
My issue was that the «File Ownership» was set to my company. I changed it to «Personal» and it worked. Right click the file and click the «File Ownership >» option and then change it to «Personal». I believe this happens with all files sent over Microsoft Teams.
answered Jan 26, 2021 at 17:46
RyanRyan
5245 silver badges14 bronze badges
If the backup destination path resides on your local machine, change the account of ‘SQL Server’ service to ‘Local System Account’, then everything must be resolved, keep in mind that the ‘SQL Server’ instance service is responsible to access the backup destination so the account it is running under, must have access to the destination path of your backup.
answered Mar 23, 2022 at 8:28
Make sure you are actually saving to a FILE and not a folder,
My problem was I was simply putting in the Folder path and not the File path
You want this
'F:Database BackupPharmacydata.bak';
You dont want this
'F:Database BackupPharmacy';
answered Oct 27, 2022 at 17:23
JamiscoJamisco
1,5833 gold badges13 silver badges17 bronze badges
Проблема
При выполнении резервного копирования или восстановления Vault (с использованием удаленного общего ресурса) на другом сервере происходит сбой резервного копирования или восстановления со следующим сообщением в пользовательском интерфейсе Autodesk Data Management Server Console:
«Autodesk Data Management Server Console 20xx»
«Не удается открыть устройство резервного копирования «».
Ошибка операционной системы 5 (доступ запрещен). Аварийное завершение работы BACKUP DATABASE.»
В файле журнала Vault (файл журнала Autodesk Data Management Server) или файле журнала резервного копирования или восстановления имеется следующая запись:
Connectivity.Application.VaultManager.ServiceException: не удается открыть устройство резервного копирования "REMOTESHAREdatabasesVault_dbbak". Ошибка операционной системы 5 (доступ запрещен). Аварийное завершение работы BACKUP DATABASE.
Причины
- Эта ошибка возникает, поскольку экземпляр MSSQL$AUTODESKVAULT выполняется под учетной записью локальной системы или сетевой службы на сервере SQL или работает под учетной записью, не являющейся доменной. У этой учетной записи недостаточно прав для доступа к удаленной общей папке, в которой будет создана резервная копия или восстановление.
- При создании вручную резервной копии баз данных из SQL Server или при восстановлении в такой же каталог появляется то же сообщение об ошибке.
Решение
- Перед выполнением резервного копирования (или восстановления) следуйте инструкциям в следующем разделе справки: Настройка удаленного местоположения резервного копирования.
- Разрешения или расположения общих ресурсов могут иногда изменяться в результате миграции или политики компании.
- Учетная запись домена, используемая для запуска Microsoft SQL Server, должна иметь полный контроль на верхнем уровне для общей сетевой папки Vault.
Программы
Vault Workgroup; Vault Professional; Vault Basic
SQL 2005 Enterprise
Even if I logon as Administrator and try to backup any of my databases to local partitions, I get this error below. I have been operating fine up until this point and doing backups all the time. As far as I know, and after checking, no permissions have changed and of course Administrator has ntfs permissions to my local C and other partitions on this server:
TITLE: Microsoft SQL Server Management Studio
——————————
Backup failed for Server ‘servername’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device ‘F:foldername’. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
——————————
BUTTONS:
OK
——————————
In the Event log for Windows Server 2003 I see this:
Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
Date: 3/6/2006
Time: 3:35:06 PM
User: NT AUTHORITYNETWORK SERVICE
Computer: servername
Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITYNETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
SQL 2005 Enterprise
Even if I logon as Administrator and try to backup any of my databases to local partitions, I get this error below. I have been operating fine up until this point and doing backups all the time. As far as I know, and after checking, no permissions have changed and of course Administrator has ntfs permissions to my local C and other partitions on this server:
TITLE: Microsoft SQL Server Management Studio
——————————
Backup failed for Server ‘servername’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device ‘F:foldername’. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
——————————
BUTTONS:
OK
——————————
In the Event log for Windows Server 2003 I see this:
Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
Date: 3/6/2006
Time: 3:35:06 PM
User: NT AUTHORITYNETWORK SERVICE
Computer: servername
Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITYNETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.