Backup Error - File in Use

I am running SQL Server on a Win 2003 Server box. Large Backups are scheduled
which are about 10gigs in size. I have a batch file that executes after the
backup is completed, which moves the 10 gb file to a different drive on the
same box. On random occassions, the backups fail with the following message:
"Unable to delete preexisting D:\Path\db.dat: The process cannot access the
file because it is being used by another process."
I am not sure what kind of locks could be set on the backup dat file to
prevent it from being deleted before the next backup. The only other program
accessing the backup file is the batch file I am running, which performs the
copy operation after each backup.
Any ideas on this will help... Thanks!Hi,
First question that arise in my mind is, why you are taking backup in one
drive and using batch cmd to move it to different drive on the same box? Why
can't you directly save your sql backup to the destination drive? With the
maintenance plan you can delete the old backups also.
Thanks
GYK|||I should have explained this in my first post. Sorry about that. The backups
are created by a SMS 2003 service. Each time the backup runs, the service
updates the contents of the folder with the latest backups. I cannot
configure it to backup to a specific location, because the process is not
user-driven. The idea of moving it to a different drive is merely for disk
space reasons and being able to store atleast 2 previous backups on a
different drive. The batch file manages this and the actual transfer of the
latest backup file.
I hope this explains things... Thanks....
"GYK" wrote:
> Hi,
> First question that arise in my mind is, why you are taking backup in one
> drive and using batch cmd to move it to different drive on the same box? Why
> can't you directly save your sql backup to the destination drive? With the
> maintenance plan you can delete the old backups also.
> Thanks
> GYK|||I find it really hard to believe you can not specify the location of the
backup. In any case do you have a tape backup process that at some point
copies that to tape? That is the most likely cause. If you can't figure
out how to change the location I would suggest you create your own scheduled
job that issues the backup in the correct place. Backing up the database to
the same drive is only asking for trouble.
--
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
>I should have explained this in my first post. Sorry about that. The
>backups
> are created by a SMS 2003 service. Each time the backup runs, the service
> updates the contents of the folder with the latest backups. I cannot
> configure it to backup to a specific location, because the process is not
> user-driven. The idea of moving it to a different drive is merely for disk
> space reasons and being able to store atleast 2 previous backups on a
> different drive. The batch file manages this and the actual transfer of
> the
> latest backup file.
> I hope this explains things... Thanks....
> "GYK" wrote:
>> Hi,
>> First question that arise in my mind is, why you are taking backup in one
>> drive and using batch cmd to move it to different drive on the same box?
>> Why
>> can't you directly save your sql backup to the destination drive? With
>> the
>> maintenance plan you can delete the old backups also.
>> Thanks
>> GYK|||Tape Backups are in place. That is a good point. I will check into this soon
and post about when they are scheduled. The issue of having the backup on the
same drive I think can be avoided through this process as we will be
archiving the last few backups on a different drive. Thanks for your
input......
"Andrew J. Kelly" wrote:
> I find it really hard to believe you can not specify the location of the
> backup. In any case do you have a tape backup process that at some point
> copies that to tape? That is the most likely cause. If you can't figure
> out how to change the location I would suggest you create your own scheduled
> job that issues the backup in the correct place. Backing up the database to
> the same drive is only asking for trouble.
> --
> Andrew J. Kelly SQL MVP
>
> "bd2103" <bd2103@.discussions.microsoft.com> wrote in message
> news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
> >I should have explained this in my first post. Sorry about that. The
> >backups
> > are created by a SMS 2003 service. Each time the backup runs, the service
> > updates the contents of the folder with the latest backups. I cannot
> > configure it to backup to a specific location, because the process is not
> > user-driven. The idea of moving it to a different drive is merely for disk
> > space reasons and being able to store atleast 2 previous backups on a
> > different drive. The batch file manages this and the actual transfer of
> > the
> > latest backup file.
> >
> > I hope this explains things... Thanks....
> >
> > "GYK" wrote:
> >
> >> Hi,
> >>
> >> First question that arise in my mind is, why you are taking backup in one
> >> drive and using batch cmd to move it to different drive on the same box?
> >> Why
> >> can't you directly save your sql backup to the destination drive? With
> >> the
> >> maintenance plan you can delete the old backups also.
> >>
> >> Thanks
> >> GYK
>
>|||It's not only when it is scheduled. I have seen tape backup software hold
locks on files for days when they get screwed up.
--
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:238DD2E4-535F-44BC-A382-473E9142B24B@.microsoft.com...
> Tape Backups are in place. That is a good point. I will check into this
> soon
> and post about when they are scheduled. The issue of having the backup on
> the
> same drive I think can be avoided through this process as we will be
> archiving the last few backups on a different drive. Thanks for your
> input......
> "Andrew J. Kelly" wrote:
>> I find it really hard to believe you can not specify the location of the
>> backup. In any case do you have a tape backup process that at some point
>> copies that to tape? That is the most likely cause. If you can't figure
>> out how to change the location I would suggest you create your own
>> scheduled
>> job that issues the backup in the correct place. Backing up the database
>> to
>> the same drive is only asking for trouble.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "bd2103" <bd2103@.discussions.microsoft.com> wrote in message
>> news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
>> >I should have explained this in my first post. Sorry about that. The
>> >backups
>> > are created by a SMS 2003 service. Each time the backup runs, the
>> > service
>> > updates the contents of the folder with the latest backups. I cannot
>> > configure it to backup to a specific location, because the process is
>> > not
>> > user-driven. The idea of moving it to a different drive is merely for
>> > disk
>> > space reasons and being able to store atleast 2 previous backups on a
>> > different drive. The batch file manages this and the actual transfer of
>> > the
>> > latest backup file.
>> >
>> > I hope this explains things... Thanks....
>> >
>> > "GYK" wrote:
>> >
>> >> Hi,
>> >>
>> >> First question that arise in my mind is, why you are taking backup in
>> >> one
>> >> drive and using batch cmd to move it to different drive on the same
>> >> box?
>> >> Why
>> >> can't you directly save your sql backup to the destination drive? With
>> >> the
>> >> maintenance plan you can delete the old backups also.
>> >>
>> >> Thanks
>> >> GYK
>>