HOWTO: Back Up an MSDE Database with T-SQL

ID: Q241397

The information in this article applies to:

Microsoft Data Engine (MSDE), version 1.0

SUMMARY

SQL Client Tools are not part of the Microsoft Data Engine (MSDE) install. Therefore, in order to back up an MSDE database, you can use the TSQL BACKUP DATABASE command as demonstrated below.

MORE INFORMATION

If you have 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, then you do not have these options. Therefore, in order to back up an MSDE database, you can use the TSQL BACKUP DATABASE command and execute with Osql.exe (command line Query tool).

For information on all of the stored procedures used below, please see SQL Books Online.

NOTE: The code below 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; database name, server name, and so forth.

Paste the following TSQL script in Notepad and save it to a file called myBackupScript.sql:


--This TSQL 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 on this option or others, reference SQL 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 TSQL Script:


OSQL -Usa -P -i myBackupScript.sql -n

You may want to schedule the job instead of running it manually. In order to do this, you can use the sample code above to create the job and then instead of calling sp_start_job to run the job manually, you can use sp_add_jobschedule to configure the job to run at a specified date/time. The job scheduling is managed by SQL Agent. This code demonstrates sp_add_jobschedule:


-- 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/delete the backup job, created above, and 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 execute the BACKUP DATABASE (or BACKUP LOG) command with OSQL directly from the command line. Using the -Q argument, OSQL executes the TSQL statement and exits. For example:


OSQL -Usa -P -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 command, please refer to SQL BOL for details at:

http://support.microsoft.com/download/support/mslfiles/sqlbol.exe.

REFERENCES

SQL Server Books Online - http://support.microsoft.com/download/support/mslfiles/sqlbol.exe
SQL Server Books Online - "BACKUP DATABASE"
SQL Server Books Online for the following references: "sp_add_job", "sp_add_jobstep", "sp_add_jobserver", "sp_start_job", "sp_add_jobschedule", "sp_delete_jobschedule", "sp_delete_job".

Additional query words: kbDSupport kbDatabase

Keywords : kbDatabase kbSQLServ kbDSupport
Version : WINDOWS:1.0
Platform : WINDOWS
Issue type : kbhowto