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...[vbcol=seagreen]
>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:
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:
sql

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
>>

Backup and restore - What is the preffered way?

What is the preferred way for Backup and Restore? Maintenance plan wizard
or command line scripts?
Also, to setup email notifications on a sql box, what do we need (outlook
client..) on the server box? Is there a step by step document online to
setup this?
Plus please post some links to backup command scripts pages.
Thanks
BVR"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
T-SQL scheduled using SQL Server Agent Service
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
You need outlook 2000 onwards installed on the box SQL is installed. SQL
Server is going to be a member of a domain and a MAPI mail server has be to
used (e.g Exchange)
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx
> Plus please post some links to backup command scripts pages.
The BOOKS Online (get the updated version from www.microsoft.com/sql) is
more then enough as a resource for writing T-SQL for Backup/Restore
> Thanks
> BVR
>
Cheers
Gill|||http://weblogs.sqlteam.com/tarad
Tara has some good backup/restore scripts. She has a standard version and a
SQL LiteSpeed version.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
> Plus please post some links to backup command scripts pages.
> Thanks
> BVR
>|||"Sarbjit Gill" <ssgill.no.spam@.no.spam.hotmail.com> wrote in message
news:%23hl3NHa9EHA.1408@.TK2MSFTNGP10.phx.gbl...
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > What is the preferred way for Backup and Restore? Maintenance plan
wizard
> > or command line scripts?
> T-SQL scheduled using SQL Server Agent Service
> >
> > Also, to setup email notifications on a sql box, what do we need
(outlook
> > client..) on the server box? Is there a step by step document online to
> > setup this?
> You need outlook 2000 onwards installed on the box SQL is installed. SQL
> Server is going to be a member of a domain and a MAPI mail server has be
to
> used (e.g Exchange)
>
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx
This is not true. Yes, you need a MAPI client, but you can seti it up to
use an SMTP server. In fact that's what I find to be the most stable, set
up an SMTP server on the same box as the SQL Server and use that to forward
outgoing email. This eliminates the problem of the mail client locking up
due to time outs.
> >
> > Plus please post some links to backup command scripts pages.
> The BOOKS Online (get the updated version from www.microsoft.com/sql) is
> more then enough as a resource for writing T-SQL for Backup/Restore
> >
> > Thanks
> > BVR
> >
> >
> Cheers
> Gill
>
>|||Thanks
BVR
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OCYipwc9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> http://weblogs.sqlteam.com/tarad
> Tara has some good backup/restore scripts. She has a standard version and
a
> SQL LiteSpeed version.
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > What is the preferred way for Backup and Restore? Maintenance plan
wizard
> > or command line scripts?
> >
> > Also, to setup email notifications on a sql box, what do we need
(outlook
> > client..) on the server box? Is there a step by step document online to
> > setup this?
> >
> > Plus please post some links to backup command scripts pages.
> >
> > Thanks
> > BVR
> >
> >
>

Backup and restore - What is the preffered way?

What is the preferred way for Backup and Restore? Maintenance plan wizard
or command line scripts?
Also, to setup email notifications on a sql box, what do we need (outlook
client..) on the server box? Is there a step by step document online to
setup this?
Plus please post some links to backup command scripts pages.
Thanks
BVR"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
T-SQL scheduled using SQL Server Agent Service
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
You need outlook 2000 onwards installed on the box SQL is installed. SQL
Server is going to be a member of a domain and a MAPI mail server has be to
used (e.g Exchange)
http://www.microsoft.com/resources/...us/default.mspx[vbco
l=seagreen]
> Plus please post some links to backup command scripts pages.[/vbcol]
The BOOKS Online (get the updated version from www.microsoft.com/sql) is
more then enough as a resource for writing T-SQL for Backup/Restore
> Thanks
> BVR
>
Cheers
Gill|||http://weblogs.sqlteam.com/tarad
Tara has some good backup/restore scripts. She has a standard version and a
SQL LiteSpeed version.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
> Plus please post some links to backup command scripts pages.
> Thanks
> BVR
>|||"Sarbjit Gill" <ssgill.no.spam@.no.spam.hotmail.com> wrote in message
news:%23hl3NHa9EHA.1408@.TK2MSFTNGP10.phx.gbl...
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
wizard[vbcol=seagreen]
> T-SQL scheduled using SQL Server Agent Service
(outlook[vbcol=seagreen]
> You need outlook 2000 onwards installed on the box SQL is installed. SQL
> Server is going to be a member of a domain and a MAPI mail server has be
to
> used (e.g Exchange)
>
http://www.microsoft.com/resources/...lt
.mspx
This is not true. Yes, you need a MAPI client, but you can seti it up to
use an SMTP server. In fact that's what I find to be the most stable, set
up an SMTP server on the same box as the SQL Server and use that to forward
outgoing email. This eliminates the problem of the mail client locking up
due to time outs.

> The BOOKS Online (get the updated version from www.microsoft.com/sql) is
> more then enough as a resource for writing T-SQL for Backup/Restore
> Cheers
> Gill
>
>|||Thanks
BVR
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OCYipwc9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> http://weblogs.sqlteam.com/tarad
> Tara has some good backup/restore scripts. She has a standard version and
a
> SQL LiteSpeed version.
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
wizard[vbcol=seagreen]
(outlook[vbcol=seagreen]
>

Backup and restore - What is the preffered way?

What is the preferred way for Backup and Restore? Maintenance plan wizard
or command line scripts?
Also, to setup email notifications on a sql box, what do we need (outlook
client..) on the server box? Is there a step by step document online to
setup this?
Plus please post some links to backup command scripts pages.
Thanks
BVR
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
T-SQL scheduled using SQL Server Agent Service
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
You need outlook 2000 onwards installed on the box SQL is installed. SQL
Server is going to be a member of a domain and a MAPI mail server has be to
used (e.g Exchange)
http://www.microsoft.com/resources/d...s/default.mspx
> Plus please post some links to backup command scripts pages.
The BOOKS Online (get the updated version from www.microsoft.com/sql) is
more then enough as a resource for writing T-SQL for Backup/Restore
> Thanks
> BVR
>
Cheers
Gill
|||http://weblogs.sqlteam.com/tarad
Tara has some good backup/restore scripts. She has a standard version and a
SQL LiteSpeed version.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
> Plus please post some links to backup command scripts pages.
> Thanks
> BVR
>
|||"Sarbjit Gill" <ssgill.no.spam@.no.spam.hotmail.com> wrote in message
news:%23hl3NHa9EHA.1408@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
wizard[vbcol=seagreen]
> T-SQL scheduled using SQL Server Agent Service
(outlook
> You need outlook 2000 onwards installed on the box SQL is installed. SQL
> Server is going to be a member of a domain and a MAPI mail server has be
to
> used (e.g Exchange)
>
http://www.microsoft.com/resources/d...s/default.mspx
This is not true. Yes, you need a MAPI client, but you can seti it up to
use an SMTP server. In fact that's what I find to be the most stable, set
up an SMTP server on the same box as the SQL Server and use that to forward
outgoing email. This eliminates the problem of the mail client locking up
due to time outs.

> The BOOKS Online (get the updated version from www.microsoft.com/sql) is
> more then enough as a resource for writing T-SQL for Backup/Restore
> Cheers
> Gill
>
>
|||Thanks
BVR
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OCYipwc9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> http://weblogs.sqlteam.com/tarad
> Tara has some good backup/restore scripts. She has a standard version and
a[vbcol=seagreen]
> SQL LiteSpeed version.
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
wizard[vbcol=seagreen]
(outlook
>