Back up Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Back up Database

Author

Topic

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-05-06 : 18:04:50

Hi,

I want to back up a database, usually i use the following script and it works.

BACK UP DATABASE 'DataBaseName'
TO DISK = C:DataBaseName.bak
GO

However I am getting the following error message to do with Back up rights i think?

Server: Msg 262, Level 14, State 1, Line 1

BACKUP DATABASE permission denied in database 'DatabaseName'.
Server: Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

I found a helpful article and granted myself permission as stated in this article http://www.sqlbackuprestore.com/backupandrestorerights.htm but still no joy. Help please

Marcus

I learn something new everyday.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-06 : 20:20:48

What that article describes appears to be correct and consistent with Microsoft documentation here: http://technet.microsoft.com/en-us/library/586561fc-dfbb-4842-84f8-204a9100a534#TsqlProcedure

Do you have permissions to write to the top of the C: drive? Even if you have, try running SSMS as administrator. Also, make sure there are no typos in your command. You have written "TO DISK = C:DataBaseName.bak". I am assuming it is a typo. You need C:\DataBaseName.bak.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-06 : 21:08:32

also don't forget the single quote around the path + filename

TO DISK = 'C:\DataBaseName.bak'

KH
[spoiler]Time is always against us[/spoiler]

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-05-07 : 02:30:06

Hi, yes I do have permissions as I have done it before with other databases from different Servers but I seem to be having problems with backing up this particular one.

Is there another I can take to write to the disk because I am not sure what the problem is.

Also, there are no typos. Just a mistake here.

Many Thanks,

Marcus

I learn something new everyday.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-07 : 11:50:51

This is a permissions issue with the database and not with the file system. What permissions do you have for that particular database? You need:

quote:

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-05-07 : 13:16:06

Assign the db_backupoperator to the logon

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources