Cannot backup transaction log files

[209912]

I trying to get this script to backup my database and log runnig under MSDE, I have been able to get the backup of my databases, but none of the transaction log wont backup. under the sql log i get only a error message stating that the log file bakcup fail. Did anybody has an idea of what i'm doing wrong here.
----------------------------------------------------
Here is the error for the log, not really helpfull

BACKUP failed to complete the command BACKUP DATABASE NCSPortal2_Prof TO DISK = 'D:\SQLSharepointBackup\NCSPortal2_Prof.dat_bak'','BACKUP DATABASE NCSPortal2_Serv TO DISK = 'D:\SQLSharepointBackup\NCSPortal2_Serv.dat_bak'','BACKUP DATABASE NCSPortal2_Site TO DISK = 'D:\SQLSharepointBackup\NCS
----------------------------------------------------------
Here is my 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 = 'MSDEBACKUPJOB',
@enabled = 1,
@description = 'MSDEBACKUPJOB',
@owner_login_name = 'sa',
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend =2,
@notify_level_page = 2
-- @notify_email_operator_name = 'raould.traore@mcgill.ca'
go

-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @job_name = 'MSDEBACKUPJOB',
@step_name = 'Backup msdb Data',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE NCSPorta2_Prof TO DISK = ''D:\SQLSharepointBackup\NCSPorta2_Prof.dat_bak'''',''BACKUP DATABASE NCSPorta2_Serv TO DISK = ''D:\SQLSharepointBackup\NCSPorta2_Serv.dat_bak'''',''BACKUP DATABASE NCSPorta2_Site TO DISK = ''D:\SQLSharepointBackup\NCSPorta2_Site.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 = 'MSDEBACKUPJOB',
@step_name = 'Backup msdb Log',
@subsystem = 'TSQL',
@command = 'BACKUP LOG NCSPorta2_Prof TO DISK = ''D:\SQLSharepointBackup\NCSPorta2_Prof.log_bak'''',''BACKUP LOG NCSPorta2_Serv TO DISK = ''D:\SQLSharepointBackup\NCSPorta2_Serv.log_bak'''',''BACKUP LOG NCSPorta2_Site TO DISK = ''D:\SQLSharepointBackup\NCSPorta2_Site.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 = 'MSDEBACKUPJOB', @server_name = N'(local)'

-- Run job. Starts the job immediately.
USE msdb
EXEC sp_add_jobschedule @job_name = 'MSDEBACKUPJOB',
@name = 'ScheduledBackup_msdb',
@freq_type = 4, --daily
@freq_interval = 1, --once
@active_start_time = '153000' --(9:30 pm) 24hr HHMMSS.


Thanks

Ralph

 

[mrdenny]

ok, I see the problem. Your backup command is all wacky.

BACKUP LOG NCSPorta2_Prof TO DISK = 'D:\SQLSharepointBackup\NCSPorta2_Prof.log_bak'
BACKUP LOG NCSPorta2_Serv TO DISK = 'D:\SQLSharepointBackup\NCSPorta2_Serv.log_bak'
BACKUP LOG NCSPorta2_Site TO DISK = 'D:\SQLSharepointBackup\NCSPorta2_Site.log_bak'

This should backup your transaction logs.

For within your code to create a job try this

@command = 'BACKUP LOG NCSPorta2_Prof TO DISK = ''D:\SQLSharepointBackup\NCSPorta2_Prof.log_bak'' BACKUP LOG NCSPorta2_Serv TO DISK = ''D:\SQLSharepointBackup\NCSPorta2_Serv.log_bak'' BACKUP LOG NCSPorta2_Site TO DISK = ''D:\SQLSharepointBackup\NCSPorta2_Site.log_bak''',

You might want to create a stored procedure that does all the backing up of the databases. That way if you add a database to the server you don't have to recreate the job to change the backups. All you have to do is change the stored procedures.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[209912]

Thanks you.

Ralph