Backup SQL via command line

[mcclunyboy]

Hi,

I am trying to automate the backup of 2 SQL Server 2000 databases.

We currently have middleware to complete the backups but I am hoping to ensure it is done automatically.

Basically Enterprise manager is not installed and I need to use a batch file and windows scheduled task to complete the backup.

Can someone either provide me with an example batch file or tell me what I have to do:

The SQL I think I need is :
"Use Master
GO
BACKUP DATABASE "mydb"
TO "c:\backups"
WITH FORMAT, CHECKSUM
GO"

I need to use OSQL and call that initially but I am not sure about it at all...

Any help is appreciated?

 

[SQL_Hell]

Why cant you install enterprise manager on a client machine, and use SQL agent jobs to do the backup?

 

[mcclunyboy]

I think a licensing issue...never the less I will investigate and see if I can install it.

In the mean time can anyone suggest the command line ?!

 

[SQL_Hell]

Ok, the sql you posted for backing up was almost right...use this

(note file name and use of INIT which overwrites the old file, remove INIT if you dont need that)

Use Master
GO
BACKUP DATABASE mydb
TO 'c:\backups\mydb.bak'
WITH FORMAT, INIT
GO

Save that as a text file with the file extension .sql
(backup.sql for example)

To run the .sql file in OSQL on the server use the following command in command prompt or scheduled task.

osql -E -i c:\backups\backup.sql

Here is an article explaining OSQL
http://msdn.microsoft.com/en-us/library/aa214012(SQL.80).aspx

If I were you...I would still try to get Enterprise manager installed...it will make your life a lot easier

 

[mcclunyboy]

its an installation of Microsoft SQL Server Desktop Engine for SQL 2000...

im trying to get the backups working now.

 

[mcclunyboy]

what do i need to add to make it work -

I received the errors:
"[Shared Memory]SQL Server does not exist or Access denied"
"[Shared Memory]SQL Server Connectionopen"

I assume I need to type password etc?!