SS 2000 Backup programatically

[Dirk McCormick]

Hi,
I am writing a remote update app, and I'd like to programmaticall y 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("hotb ackup mydb c:\tmp\backups\ todaysBackup.ba k")

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\)]

Re: SS 2000 Backup programatically


"Dirk McCormick" <dirk@acquirer. nospam.com> wrote in message
news:9d35b38b05 61c1a8a531e08db 9f296b4@news.me ganetnews.com.. .[color=blue]
> Hi,
> I am writing a remote update app, and I'd like to programmaticall y 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("hotb ackup mydb c:\tmp\backups\ todaysBackup.ba k")[/color]

You do it from within SQL Server.

Look for backup database in Books On-Line.

[color=blue]
>
> I'm not concerned with how long it takes to restore. ie preferably a quick
> backup and if necessary a long restore.
> Cheers,
> Dirk
>
>[/color]

[Dirk McCormick]

Re: SS 2000 Backup programatically

> > I am writing a remote update app, and I'd like to programmaticall y
backup[color=blue][color=green]
> > the SQL Server 2000 database before running the SQL files over it.
> > ...[/color][/color]
[color=blue]
> You do it from within SQL Server.
>
> Look for backup database in Books On-Line.[/color]


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("hotb ackup mydb c:\tmp\backups\ todaysBackup.ba k", ...);
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\)]

Re: SS 2000 Backup programatically


"Dirk McCormick" <dirk@acquirer. nospam.com> wrote in message
news:da8b93526d 0a774fba8046e0b 54ef9b3@news.me ganetnews.com.. .[color=blue][color=green][color=darkred]
> > > I am writing a remote update app, and I'd like to programmaticall y[/color][/color]
> backup[color=green][color=darkred]
> > > the SQL Server 2000 database before running the SQL files over it.
> > > ...[/color][/color]
>[color=green]
> > You do it from within SQL Server.
> >
> > Look for backup database in Books On-Line.[/color]
>
>
> 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[/color]
the[color=blue]
> database. So before this happens, I want the program to backup the[/color]
database[color=blue]
> from code.[/color]

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

No human intervention required.
[color=blue]
>
> eg
>
> getSQLFiles();
> ShellExec("hotb ackup mydb c:\tmp\backups\ todaysBackup.ba k", ...);
> 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
>
>[/color]

[Dirk McCormick]

Re: SS 2000 Backup programatically

> Right. You write the backup command as a script and execute that as you[color=blue]
> would your other scripts.
>
> No human intervention required.[/color]

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]

Re: SS 2000 Backup programatically

>> Right. You write the backup command as a script and execute that as you[color=blue][color=green]
>> would your other scripts.
>>
>> No human intervention required.[/color]
>
>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
>[/color]

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\BA CKUP\MyNwind_1. dat'

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




Randy

http://members.aol.com/rsmeiner

[Dirk McCormick]

Re: SS 2000 Backup programatically

Perfect.
Thanks!
dirk
[color=blue]
> 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[/color]
Server\MSSQL\BA CKUP\MyNwind_1. dat'[color=blue]
>
> -- Back up the full MyNwind database.
> BACKUP DATABASE MyNwind TO MyNwind_1[/color]

[Ian Stocks]

Re: SS 2000 Backup programatically

"RSMEINER" <rsmeiner@aol.c omcrap> wrote in message
news:2004050609 4516.01198.0000 0851@mb-m14.aol.com...[color=blue][color=green][color=darkred]
> >> Right. You write the backup command as a script and execute that as[/color][/color][/color]
you[color=blue][color=green][color=darkred]
> >> would your other scripts.
> >>
> >> No human intervention required.[/color]
> >
> >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[/color][/color]
be[color=blue][color=green]
> >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
> >[/color]
>
> 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[/color]
Server\MSSQL\BA CKUP\MyNwind_1. dat'[color=blue]
>
> -- Back up the full MyNwind database.
> BACKUP DATABASE MyNwind TO MyNwind_1[/color]

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\BA CKUP\MyNwind_1. dat'

Ian.