How 2 BACKUP using SQL ?

[Yovav]

Hi,

I understand that it is possible 2 backup / restore database WITHOUT using enterprise manager,
(on SQL Server 2000)

does anyone know the syntax ?
(something like: BACKUP table to file)
:(

[whammy]

I wish I could help you with that, but I don't do the backups at work. :(

However, if noone answers, I will know how in a few months, since this should be in one of the classes I'm taking. :|

I'm sure someone here can help you out, though. :)

[Yovav]

on SQL Server Books Online:

Administering SQL Server ->
Importing and Exporting Data ->
Choosing a Tool to Import or Export Data

And

BACKUP
Backs up an entire database, transaction log, or one or more files or filegroups. For more information about database backup and restore operations, see Backing Up and Restoring Databases.

Syntax
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 ] ]
]



but still, is it possible to backup from database located on a server to a file on my local computer ?

[rcreyes]

I got this from the Microsoft site:

SUMMARY
SQL Client Tools are not part of the Microsoft Data Engine (MSDE) installation. Therefore, to back up an MSDE database, you can use the Transact-SQL BACKUP DATABASE statement, which is discussed in this article.
MORE INFORMATION
If you have Microsoft Access 2000, you can use the BACKUP command in the Database Utilities menu of an Access project to back up an MSDE database. If SQL Client Tools are installed, you can use SQL Enterprise Manager to back up an MSDE database.

However, if you only have MSDE installed, you do not have these options. Therefore, to back up an MSDE database, you can use the Transact-SQL BACKUP DATABASE statement, and run Osql.exe (a command line Query tool).

For information about all of the stored procedures used in this article, see SQL Server Books Online.

NOTE: The following code is an example of how to use the various stored procedures with MSDE to perform a backup. This code does not back up your mission critical database "as is" and might require some modification to run in your environment. For example, database name, server name, and so forth.
Paste the following Transact-SQL script in Notepad, and then save it to a file named MyBackupScript.sql:
--This Transact-SQL script creates a backup job and calls sp_start_job to run the job.

-- Create job.
-- You may specify an e-mail address, commented below, and/or pager, etc.
-- For more details about this option or others, see SQL Server Books Online.
USE msdb
EXEC sp_add_job @job_name = 'myTestBackupJob',
@enabled = 1,
@description = 'myTestBackupJob',
@owner_login_name = 'sa',
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend =2,
@notify_level_page = 2
-- @notify_email_operator_name = 'email name'
go

-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
@step_name = 'Backup msdb Data',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE msdb TO DISK = ''c:\msdb.dat_bak''',
@on_success_action = 3,
@retry_attempts = 5,
@retry_interval = 5
go

-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
@step_name = 'Backup msdb Log',
@subsystem = 'TSQL',
@command = 'BACKUP LOG msdb TO DISK = ''c:\msdb.log_bak''',
@on_success_action = 1,
@retry_attempts = 5,
@retry_interval = 5
go

-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @job_name = 'myTestBackupJob', @server_name = N'(local)'

-- Run job. Starts the job immediately.
USE msdb
EXEC sp_start_job @job_name = 'myTestBackupJob'

From the command line, use the following osql syntax to run the Transact-SQL script:
OSQL -Usa -PmyPasword -i myBackupScript.sql -n

You may want to schedule the job instead of running it manually. To do this, you can use the earlier code to create the job. Instead of calling the sp_start_job stored procedure to run the job manually, you can use the sp_add_jobschedule stored procedure to configure the job to run at a specified date and time. The SQL Agent manages the job scheduling. This code demonstrates use of the sp_add_jobschedule stored procedure:
-- Use the job creation code from the previous sample and call sp_add_jobschedule instead of sp_start_job.
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @job_name = 'myTestBackupJob',
@name = 'ScheduledBackup_msdb',
@freq_type = 4, --daily
@freq_interval = 1, --once
@active_start_time = '153000' --(3:30 pm) 24hr HHMMSS.
go

--Use the following code to remove or delete the backup job, created earlier, and then remove the job from the Scheduler.
-- Delete scheduled job.
USE msdb
EXEC sp_delete_jobschedule @job_name = N'myTestBackupJob',
@name = N'ScheduledBackup_msdb'

-- Delete job.
USE msdb
EXEC sp_delete_job @job_name = N'myTestBackupJob'

If you do not want to create a backup job or a scheduled backup, you can run the BACKUP DATABASE (or BACKUP LOG) statement with osql directly from the command line. If you use the -Q argument, osql runs the Transact-SQL statement, and then exits. For example:
OSQL -Usa -PmyPasword -n -Q "BACKUP DATABASE msdb TO DISK = 'c:\msdb.dat_bak'"

[whammy]

You could also backup your database automatically to another table (or a file) or whatever using a DTS package if you're using SQL Server.

If you were using something like Access, you could export the data to a .csv file on your local machine using a .vbs script or an asp script.