Peter J. Boettcher — Tue, 10 Apr 2001 18:30:10 +0000

You're going to have to use the built in sytem stored procedures of SQL 2000. You'll need to use the following:

sp_add_job
sp_add_jobstep
sp_add_jobschedule

If you're not comfortable with Stored Procedures then I wouldn't recommend going any further since system stored proedures can really mess up your SQL Server if used poorly.

You would have to setup a stored procedure that would call those 3 stored procedures, in the jobstep you would backup the database, in the jobschedule you would setup the schedule of the backup.

If you've got SQL Books online installed you can find more help for these stored procedures.

Jacost — Tue, 10 Apr 2001 15:28:52 +0000

I don't know how to program sql so I wonder if you could help me to make the following code work as we want. It will run the first time from query analyzer but then should be able to keep on his own backups (using different name for each record)If I leave the same name of "disk" - "backupfile.dat" return errors.
Sorry if I'm annoying with all these questions but I have to find a solution till tonight and I'm totally inrelative with the subject....

-- Create the backup device for the full testdb backup.
USE testdb

EXEC sp_addumpdevice 'disk', 'testdb2',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\testdb2.dat'

--Create the log backup device.
USE testdb
EXEC sp_addumpdevice 'disk', 'testdbLog',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\testdbLog.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE testdb TO testdb2
WITH
RETAINDAYS=7

-- Update activity has occurred since the full database backup.

-- Back up the log of the MyNwind database.
BACKUP LOG testdb
TO testdbLog

Peter J. Boettcher — Tue, 10 Apr 2001 13:32:27 +0000

That's the Transact-SQL syntax for BACKUP DATABASE/LOG. This will not execute automatically, it must be triggered either by user or job:

'Define where to dump database data
EXEC sp_addumpdevice 'disk', 'My_Backup',
DISK ='c:\path\My_Database.dat'

'Back up My_Database
BACKUP DATABASE My_Database TO My_Backup

If you also want to backup the log just add a BACKUP LOG using the same syntax above.

Jacost — Tue, 10 Apr 2001 13:15:45 +0000

The point to do this that I set up a web server for people
with no experience at all in computers and I have to tell them to run 2-3 script that will create their DB and that's why I need also a script for keep backup.The will run it once and then they will not have to monitoring anything.

I found this but I can't understand it very well

Backing up an entire database:

BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

Backing up specific files or filegroups:

BACKUP DATABASE { database_name | @database_name_var }
< file_or_filegroup > [ ,...n ]
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

Backing up a transaction log:

BACKUP LOG { database_name | @database_name_var }
{
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ ,] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
}

< backup_device > ::=
{
{ logical_backup_device_name | @logical_backup_device_name_var }
|
{ DISK | TAPE } =
{ 'physical_backup_device_name' | @physical_backup_device_name_var }
}

< file_or_filegroup > ::=
{
FILE = { logical_file_name | @logical_file_name_var }
|
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
}

Truncating the transaction log:

BACKUP LOG { database_name | @database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}

Peter J. Boettcher — Tue, 10 Apr 2001 12:38:32 +0000

This is not going to work to good. Is there any reason you can't use the built in backup job in SQL? It doesn't require any user intervention (unless tape/destination is full)

Even with script or a component something will have to trigger it to run, unless it's constantly running. Even then the backups might not always work since SQL locks many of the files and they won't be available to be backed up.

The only other way would be to have a mirror of the database and then run ASP/Stored procs that copy any new data into your backup database.