How to back up a Microsoft Data Engine database by using Transact-SQL

Article translations Article translations
Article ID: 241397
Expand all | Collapse all

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

References

To download an updated version of SQL Server 2000 Books Online, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/sqlserver/bb331756.aspx
To download the Microsoft SQL Server 7.0 Books Online version, visit the following Microsoft Web site:
http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/SQLBOL.exe
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"

Properties

Article ID: 241397 - Last Review: June 19, 2014 - Revision: 5.0
Keywords: 
kbdownload kbdatabase kbhowto KB241397
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com