back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.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 = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK =
''c:\backups\Tuesday_databasebackup.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 = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK =
''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
goApparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegroups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.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 = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK =
> ''c:\backups\Tuesday_databasebackup.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 = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK =
> ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>