Backup Through Job

Question

Post reply

Backup Through Job

mca.vimal

Ten Centuries

Points: 1050
More actions
November 9, 2010 at 3:25 am

#146012

Dear All,
hi this is vimal kumar and i am working as a database admin,Dot net Programmer, Window Serve Administrator.
when i teken bakup of my databses through job i got serval erros and the job failed.
NT AUTHORITY\SYSTEM. Cannot open backup device 'D:\payrbkp\pay_01_07'. Operating system error 32(The process cannot access the file because it is being used by another process.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The owner (domailname\vimal.kumar) of job payroll1 does not have server access
please help me out this problem,
and also send me the Proper document of SQL Admin ...............
my mail is mcavimal@gmail.com
mcavimal@yahoo.com
Vimal Kumar
9999797952

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

did u try to backup the database from SSMS?

Pradeep Singh

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

It's best to make each new backup to a new backup device (file). That way, you won't have contention for the use of that file. You say you're doing that through a job. Is it a maintenance plan? If not, please post the command you're using to do the backup.

John

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Check to see whether or not there is a tape backup job that is backing up that file to tape.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mca.vimal Ten Centuries Points: 1050 More actions · Answer 4

hi John,

i am taking the Backup through maintance plan

and i always got the error as i maintion abobe ....

i also chage the owner of the job sa, not working sane error comes out

Vimal Kumar
9999797952

ps. One Orange Chip Points: 29422 More actions · Answer 5

Pls run the following command and see if backup runs successfully.

backup database yourdb to disk='D:\payrbkp\yourdb.bak'

Pradeep Singh

mca.vimal Ten Centuries Points: 1050 More actions · Answer 6

BACKUP DATABASE [payroll_01_07] TO DISK = N'D:\payrbkp\pay_01_07' WITH NOFORMAT, NOINIT, NAME = N'payroll_01_07-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

i am using this

Vimal Kumar
9999797952

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

mcavimal-736871 (11/9/2010)
hi John,
i am taking the Backup through maintance plan
and i always got the error as i maintion abobe ....
i also chage the owner of the job sa, not working sane error comes out

Are you backing up to the same file, or to a different one each time?

John

mca.vimal Ten Centuries Points: 1050 More actions · Answer 8

mca.vimal

Ten Centuries

Points: 1050

November 10, 2010 at 1:53 am

#1248710

only one file

Vimal Kumar
9999797952

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Is there any reason for that? As I said before, I would recommend that you back up to a new file each time. That way, if any file becomes corrupted or inaccessible for any reason, it only affects one backup and not all of them.

John

mca.vimal Ten Centuries Points: 1050 More actions · Answer 10

john,

i mean to say i am taking only one DB backup at a time .

the first step i s running successful ...

after that i got the error.

i also have the same problem with my onther dB but when i change the Owner of the job it's working fine ..

i am still facing the problem with the new Backup Job for other DB.

Vimal Kumar
9999797952

mca.vimal Ten Centuries Points: 1050 More actions · Answer 11

i delete all backup file from the folder and then i going to take backup but still i got the problem..

Vimal Kumar
9999797952

ps. One Orange Chip Points: 29422 More actions · Answer 12

I would agree with John. You need to take backup of the database to separate files each time. Currently, you are taking backup in a single file which means it will have multiple backups of your database.

you can append a string with something like this in the backup file name.

convert(varchar, getdate(),112) + "_" + replace(convert (varchar, getdate(),108),':','') + '.BAK'

This will always give you a new file name.

Pradeep Singh

mca.vimal Ten Centuries Points: 1050 More actions · Answer 13

thanks John & pradeep,

i have got the right solution for it & correct my job

now i have taken 9 database backup at a time through job , i have allready make nine jobs for nine batabase and they are working fime now.

once again thanks

i have ome more problem when the job is running at my login meams when i am the owner of the job it fails and when i chage the owner like sa it runs succefully.... please help me out.

Vimal Kumar
9999797952

ps. One Orange Chip Points: 29422 More actions · Answer 14

actually 9 jobs were not required. 1 job could have backed up all nine databases one by one.

For other issue, it looks to me like permission issue. Pls check if the service account used by sqlserveragent has permission on the folder where you are taking backup.

Pradeep Singh