cancel
Showing results for 
Search instead for 
Did you mean: 

SQL online backup.

Former Member
0 Kudos

Hi all,

How can i initiate the online backup for SQL database.

and please provide me the sql commands for sql backup.

Thanks in advance.

vinnu.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

You can schedule the backup via the SQL Server gui.

right click the DB name and select backup from the resulting list.

run though and tick the boxes, set the timings and days. Save.

Then to see the SQL goto the SQL agent, job, and open the job you saved above.

Look in/at the steps.

This will give you the code.

However sample code is below

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