May 19, 2011 at 3:18 am
hi,
when i am taking full back full through syntax i get following error:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\test_k.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
what should i do?
May 19, 2011 at 3:24 am
Make sure your SQL Server service account and/or SQL Server Agent service account have write access to the location to which you are trying to back up.
John
May 19, 2011 at 3:31 am
i have all permissions .i am taking the backup my office server but i take with GUI with default path of the 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup' it accepts
May 19, 2011 at 3:37 am
Where is the drive to which you are backing up (D) - on your office server or on the SQL Server? If it's not on the SQL Server then you need to provide a UNC path.
John
May 19, 2011 at 3:48 am
kishorechowdary.erp (5/19/2011)
hi,when i am taking full back full through syntax i get following error:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\test_k.bak'. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
what should i do?
The error clearly says, Check what John suggest
Also try tis xp_cmdshell 'dir 'D:\'
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 19, 2011 at 6:33 am
put your backups into a sub directory. In some circumstances you cannot write files to a root of a drive. ( it's sloppy practice to write stuff to a root anyway )
try a subfolder.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 20, 2011 at 4:24 am
check the SQL Server Service account rights on SQL Server or OS level too
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 20, 2011 at 5:32 am
If it lets you backup to the default backup location I would definitely say there's a permissions issue on the D:\.
What if you create a test folder, ie, D:\Test and try backing it up to there ?
May 26, 2011 at 4:06 am
hi sir ,
i am taking backup from sql server authentication from my system in office but it is throughing error.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\test_k.bak'. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
can i have a t-sql script by which i can take the backup from my system .
May 26, 2011 at 4:18 am
Sounds to me like you have the same problem as kishorechowdary.erp
Check the permissions on the drive, and if it still fails, try creating a test folder and backup to there.
May 26, 2011 at 4:20 am
Check if SQL Service account have permission to the backup folder, also if the user account you have logged in have the permission. As you specified you are conencting from the the office DB server and trying to take the backup , Im sure it will be permission issue.
You can use this script:
/*
This script will perform full database backup with the database name
and filename as parameter. This will backup to the specified folder
D:\Test_Perf_DB_Backup folder
Author : Minaz Amin
Execution statement :
Exec db_backup_script @DBname = <Databasename>,@filename = <Backup filename>
*/
Alter proc DB_backup_script
(@DBname varchar(100),
@filename varchar(200)
)
As
begin
DECLARE @sql VARCHAR(1000), @cmd varchar(1000),@verify varchar(1000)
SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''D:\PERFDB_BACKUP_TESTDATA'+ @filename +'.BAK'' WITH INIT,checksum,stop_on_error'
EXEC (@SQL)
SET @cmd = 'D:\'+ @filename +'.BAK'
select @verify='RESTORE VERIFYONLY FROM DISK = ''' + @cmd + ''''
EXEC (@verify)
END
"More Green More Oxygen !! Plant a tree today"
May 26, 2011 at 4:24 am
permission in windows server drive or my local system drive can u plz say how to do it plz provide me the details
May 26, 2011 at 4:27 am
Permission on the DB server D drive backup folder.
"More Green More Oxygen !! Plant a tree today"
May 26, 2011 at 4:33 am
we don'nt have db server in our office & we have hosted our database and every thing in a 3rd party server or u can say data center.
i access the server database from my laptop in the office by sql server authentication logins
by using the userid and paasword.
May 26, 2011 at 4:35 am
Then that's your problem. As I said in an earlier post, if it's not on your SQL Server, you need to use a UNC.
John
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply