Schedule to backup MSDE 2000 to overwrite the previous file

[EX Admin]

Hello,

I am try to backup MSDE database with the following command:

OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'"

It works, however, once I use windows scheduler to schedule to run the
script daily,

it appends to the file instead to overwrite it. Is there any way to
overwrite the existing file automatically?

Thanks,

[Lawrence Garvin]

Post by EX Admin
Hello,
OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'"
It works, however, once I use windows scheduler to schedule to run the
script daily,
it appends to the file instead to overwrite it. Is there any way to
overwrite the existing file automatically?

You need to add the WITH INIT clause to the command to force it to
"initialize" the backup file (i.e. not append).

osql -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak' WITH INIT

[EX Admin]

Thank you very much! it works. However, can I create a backup file on remote
share with the following command:

osql -E -Q "BACKUP DATABASE MYDB TO DISK = '\\myserver\sqlbak\MYDB.bak' WITH
INIT"

I got access denied to backup device '\\myserver\sqlbak\MYDB.bak'

Thanks again,

Post by Lawrence Garvin

Post by EX Admin
Hello,
OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'"
It works, however, once I use windows scheduler to schedule to run the
script daily,
it appends to the file instead to overwrite it. Is there any way to
overwrite the existing file automatically?

You need to add the WITH INIT clause to the command to force it to
"initialize" the backup file (i.e. not append).
osql -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak' WITH INIT
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas

[Russell Fields]

The account that your SQL Server is running under must have rights to the
path. If you are running as Local System then you will not have those
rights.

Run the SQL Server using a domain account with appropriate rights to the
path. Then the backup will be able to reach outside the server.

RLF

Post by EX Admin
Thank you very much! it works. However, can I create a backup file on remote
osql -E -Q "BACKUP DATABASE MYDB TO DISK = '\\myserver\sqlbak\MYDB.bak' WITH
INIT"
I got access denied to backup device '\\myserver\sqlbak\MYDB.bak'
Thanks again,

Post by Lawrence Garvin

Post by EX Admin
Hello,
OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'"
It works, however, once I use windows scheduler to schedule to run the
script daily,
it appends to the file instead to overwrite it. Is there any way to
overwrite the existing file automatically?

You need to add the WITH INIT clause to the command to force it to
"initialize" the backup file (i.e. not append).
osql -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak' WITH INIT
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas

[EX Admin]

thanks for your both reponses.

Post by Russell Fields
The account that your SQL Server is running under must have rights to the
path. If you are running as Local System then you will not have those
rights.
Run the SQL Server using a domain account with appropriate rights to the
path. Then the backup will be able to reach outside the server.
RLF

Post by EX Admin
Thank you very much! it works. However, can I create a backup file on remote
osql -E -Q "BACKUP DATABASE MYDB TO DISK = '\\myserver\sqlbak\MYDB.bak' WITH
INIT"
I got access denied to backup device '\\myserver\sqlbak\MYDB.bak'
Thanks again,

Post by Lawrence Garvin

Post by EX Admin
Hello,
OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'"
It works, however, once I use windows scheduler to schedule to run the
script daily,
it appends to the file instead to overwrite it. Is there any way to
overwrite the existing file automatically?

You need to add the WITH INIT clause to the command to force it to
"initialize" the backup file (i.e. not append).
osql -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak' WITH INIT
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas