1

I am trying to solve a backup problem for SQL Server 2012 for some time now.

The problem is I not able to make backup TO DISK, I think it is directory error

Query used:

BACKUP DATABASE [ProjectDatabase] 
TO DISK = 'C:\Users\AizazHussain\Dropbox\TestProjectBackup.bak';

OR

BACKUP DATABASE [ProjectDatabase] 
TO DISK ='C:\TestProjectBackup.bak';

Both attempts failed.

If I use the directory C:\BackupFolder\TestProjectBackup.bak, it works fine. Somewhere on Stack overflow there was a solution to create backup in a folder so I created BackupFolder in C:\ and it works fine.

Note: it worked fine for other partitions

e.g. 

BACKUP DATABASE [ProjectDatabase] 
TO DISK ='D:\TestProjectBackup.bak';

BACKUP DATABASE [ProjectDatabase] 
TO DISK ='D:\Folder\TestProjectBackup.bak';

// It works but only for C:\

Pardon me if there are any grammatical mistake in my question

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ezaz
  • 183
  • 1
  • 11
  • "It fails however I If I use the directory 'C:\BackupFolder\TestProjectBackup.bak' it works fine " ... this statement makes no sense ... you say it fails, then it works fine. Which is it? – Patrick Oct 14 '17 at 00:48
  • I think it is good now I have made edits so you can understand more easily, please ignore my grammatical mistake, if any. Thank You @Patrick – Ezaz Oct 14 '17 at 01:34
  • You have windows installed on C drive? What is the error you get? – Amit Kumar Singh Oct 14 '17 at 01:37
  • Yes I have Installed windows on C drive. Error is Cannot open backup device 'C:\TestProject.bak'. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally. – Ezaz Oct 14 '17 at 01:41
  • Does this help? https://sqlbackupandftp.com/blog/cannot-open-backup-device-operating-system-error-5access-is-denied – Patrick Oct 14 '17 at 01:45
  • You should ask this on https://superuser.com/. It should be related to UAC (User account control) feature of windows and windows security features. Windows, since vista, generally protects files on the partition where windows is installed. And file creation through software is not allowed by default. check if this helps. https://stackoverflow.com/questions/18286765/sql-server-operating-system-error-5-5access-is-denied – Amit Kumar Singh Oct 14 '17 at 02:11
  • Thank you @Amit I will also ask on superuser.com – Ezaz Oct 14 '17 at 02:21
  • @Patrick I already checked permission, settings are the same as they said, but the error remains – Ezaz Oct 14 '17 at 02:22

1 Answers1

2

SQL Server executes BACKUP DATABASE command under the context of the service account. This account is usually MSSQLSERVER. This account is configured in Services:

MSSQLSERVER service properties - logon tab

This account must have Modify permissions to the backup target directory:

SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions.

(Source: BACKUP (Transact-SQL))

Check your NTFS permissions.

Serge
  • 3,986
  • 2
  • 17
  • 37