Most backup programs without open file option will not backup the database while it's running.
You will need to stop the server with batch file containing: Net Stop Mssqlserver or export the database to a bck file and then capture that with your backup solution. You can do that with Enterprise Manager from the SQL Trial, or use suggestions from the following sql script.
--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 provot Data',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE madison TO DISK = ''c:\backup\madison.bck''',
@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 provot Log',
@subsystem = 'TSQL',
@command = 'BACKUP LOG madison TO DISK = ''c:\backup\madison.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
USE msdb
EXEC sp_add_jobschedule @job_name = 'myTestBackupJob',
@name = 'ScheduledBackup_msdb',
@freq_type = 4, --daily
@freq_interval = 1, --once
@active_start_time = '220000' --(3:30 pm) 24hr HHMMSS
Share
Report
100
All responses (
Answers (
