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.

Back to the top

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'"
				

Because there are many other options you might want to specify with the BACKUP DATABASE statement, see SQL Server Books Online for more details:

http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/sqlbol.exe (http://download.microsoft.com/download/sql70/file/2/win98/en-us/sqlbol.exe)

Back to the top

To download an updated version of SQL Server 2000 Books Online, visit the following Microsoft Web site: To download the Microsoft SQL Server 7.0 Books Online version, visit the following Microsoft Web site: SQL Server Books Online: topics: "Database Backups"; "sp_add_job"; "sp_add_jobstep"; "sp_add_jobserver"; "sp_start_job"; "sp_add_jobschedule"; "sp_delete_jobschedule"; "sp_delete_job"

Back to the top