SQL backup statement

[risoam]

I have an active server page based website that is used for scheduling of an EMS department. The site is hosted by a web host company. The backend database is SQL Server 2000. The web host set up an FTP account for us so that when we backup the database itself, we can FTP in and download the backup database file, just incase. Using the enterprise manager I can backup the database, but they dont support scheduling of the backup jobs, so it is a manual process for us. We can get an automated FTP program to mirror the site, but I need a way to backup the database automatically. Is there a SQL statment to backup a database. I can check to see if it was run yet, so the first time the page is accessed or something I can have it do it. I would be backing the file up to disk on a drive like d:\ftp\sitename\db.bak or something like that. Thanksf for your help.

Adam

 

[osjohnm]

Hi , I have never done this myself, I always use a DB Maintenance plan which is executed by a job.

USE master
EXEC sp_addumpdevice 'disk', 'DB',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DB_1.dat'

-- Back up the full database.
BACKUP DATABASE DB1 TO DB_1

You will find a more detailed explanation of this in BOL under 'BACKUP DATABASE'. I got the above syntax from BOL.

Hope this helps a bit.

 

[osjohnm]

Hi, me again

I tried the above statement from BOL but I couldn't get it to function. I managed to get the following to work:

USE iData
GO
BACKUP DATABASE iData
TO DISK = 'C:\Program Files\Microsoft'
WITH FORMAT,
NAME = 'Full Backup of iData'
GO