SQL Server database backup error

Question

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

"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
– Patrick, Commented Oct 14, 2017 at 0: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
– Ezaz, Commented Oct 14, 2017 at 1:34
You have windows installed on C drive? What is the error you get? — Amit Kumar Singh
– Amit Kumar Singh, Commented Oct 14, 2017 at 1: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
– Ezaz, Commented Oct 14, 2017 at 1:41

Serge · Accepted Answer · 2017-10-14 06:37:41Z

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.

edited Oct 14, 2017 at 6:37

answered Oct 14, 2017 at 6:21

Serge

4,0662 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server database backup error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related