SS 2000 Backup programatically

[Dirk McCormick]

Hi,
I am writing a remote update app, and I'd like to programmatically backup
the SQL Server 2000 database before running the SQL files over it.
Can anyone tell me a command that does a hot backup of the DB and puts it
somewhere, which I would shell exec from my program.

eg
ShellExec("hotbackup mydb c:\tmp\backups\todaysBackup.bak")

I'm not concerned with how long it takes to restore. ie preferably a quick
backup and if necessary a long restore.
Cheers,
Dirk

[Greg D. Moore \(Strider\)]

"Dirk McCormick" <di**@acquirer.nospam.com> wrote in message
news:9d******************************@news.meganet news.com...

Hi,
I am writing a remote update app, and I'd like to programmatically backup
the SQL Server 2000 database before running the SQL files over it.
Can anyone tell me a command that does a hot backup of the DB and puts it
somewhere, which I would shell exec from my program.

eg
ShellExec("hotbackup mydb c:\tmp\backups\todaysBackup.bak")
You do it from within SQL Server.

Look for backup database in Books On-Line.


I'm not concerned with how long it takes to restore. ie preferably a quick
backup and if necessary a long restore.
Cheers,
Dirk

[Dirk McCormick]

> > I am writing a remote update app, and I'd like to programmatically
backup

the SQL Server 2000 database before running the SQL files over it.
...

You do it from within SQL Server.

Look for backup database in Books On-Line.

Actually I want to do it from within the remote update program, ie
automatically, no user intervention.
The program downloads sql scripts from the internet, and runs them over the
database. So before this happens, I want the program to backup the database
from code.

eg

getSQLFiles();
ShellExec("hotbackup mydb c:\tmp\backups\todaysBackup.bak", ...);
executeSQLFiles();

An online search for how to backup SQL server only gives me manual methods
and training courses. I am assuming, perhaps incorrectly, that there is
command line utility that can do hot backups.
Cheers,
Dirk

[Greg D. Moore \(Strider\)]

"Dirk McCormick" <di**@acquirer.nospam.com> wrote in message
news:da******************************@news.meganet news.com...

I am writing a remote update app, and I'd like to programmatically backup the SQL Server 2000 database before running the SQL files over it.
...

You do it from within SQL Server.

Look for backup database in Books On-Line.

Actually I want to do it from within the remote update program, ie
automatically, no user intervention.
The program downloads sql scripts from the internet, and runs them over

the database. So before this happens, I want the program to backup the database from code.
Right. You write the backup command as a script and execute that as you
would your other scripts.

No human intervention required.

eg

getSQLFiles();
ShellExec("hotbackup mydb c:\tmp\backups\todaysBackup.bak", ...);
executeSQLFiles();

An online search for how to backup SQL server only gives me manual methods
and training courses. I am assuming, perhaps incorrectly, that there is
command line utility that can do hot backups.
Cheers,
Dirk

[Dirk McCormick]

> Right. You write the backup command as a script and execute that as you

would your other scripts.

No human intervention required.

I suppose I could write a script to copy the existing tables to temporary
tables, but I would prefer to actually output the data to a file.
Is this what you mean, or do you mean send SQL commands that backup the
database? (eg "BACKUP DBNAME TO <FILEPATH>")
I don't know of any such commands, but if there are this solution would be
fine.
I should point out by the way that the SQL scripts I'm running are not
PL/SQL or any other SQL programming script, just plain old DDL and SQL
As you may have gathered I don't know much about databases, so pretend
you're talking to a novice =)

Thanks for your help.
Dirk

[RSMEINER]

>> Right. You write the backup command as a script and execute that as you

would your other scripts.

No human intervention required.

I suppose I could write a script to copy the existing tables to temporary
tables, but I would prefer to actually output the data to a file.
Is this what you mean, or do you mean send SQL commands that backup the
database? (eg "BACKUP DBNAME TO <FILEPATH>")
I don't know of any such commands, but if there are this solution would be
fine.
I should point out by the way that the SQL scripts I'm running are not
PL/SQL or any other SQL programming script, just plain old DDL and SQL
As you may have gathered I don't know much about databases, so pretend
you're talking to a novice =)

Thanks for your help.
Dirk

There is a backup database command that you can
run thru Tsql. Here is what is in the Tsql online documentation.

-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1


Randy
http://members.aol.com/rsmeiner

[Dirk McCormick]

Perfect.
Thanks!
dirk

There is a backup database command that you can
run thru Tsql. Here is what is in the Tsql online documentation.

-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1

[Ian Stocks]

"RSMEINER" <rs******@aol.comcrap> wrote in message
news:20***************************@mb-m14.aol.com...

Right. You write the backup command as a script and execute that as you would your other scripts.

No human intervention required.
I suppose I could write a script to copy the existing tables to temporary
tables, but I would prefer to actually output the data to a file.
Is this what you mean, or do you mean send SQL commands that backup the
database? (eg "BACKUP DBNAME TO <FILEPATH>")
I don't know of any such commands, but if there are this solution would befine.
I should point out by the way that the SQL scripts I'm running are not
PL/SQL or any other SQL programming script, just plain old DDL and SQL
As you may have gathered I don't know much about databases, so pretend
you're talking to a novice =)

Thanks for your help.
Dirk

There is a backup database command that you can
run thru Tsql. Here is what is in the Tsql online documentation.

-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL

Server\MSSQL\BACKUP\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1

You dont need to add a dump device, you can backup direct to a file :-

BACKUP DATABASE MyNwind to disk = 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MyNwind_1.dat'

Ian.