SQL Script Help

[A[L]C]

Hi all,

I have some code to automate the backup of a MSDE Sql database which works fine. However I would like to change the backup filename to be a variable of Server + datetimestamp.bak. Is this possible? If so, what do I need to change?

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

Thanks in advance

 

[A[L]C]

Thanks for that. I've been playing but it runs ok the first time but will not start the second time even if I delete the previous .bak file.

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

-- Create backup filename
declare @FileName nvarchar(100)
set @FileName = 'C:\' + @@SERVERNAME + '_weighman_' + CONVERT(varchar, getdate(), 112)
print @FileName

declare @Command nvarchar(255)

set @Command = 'declare @FileName nvarchar(100);set @FileName = ''C:\'' + @@SERVERNAME + ''_weighman_'' + CONVERT(varchar, getdate(), 112) + ''.bak''; BACKUP DATABASE weighman TO DISK = @FileName'
-- Add job step (backup data).
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
    @step_name = 'Backup weighman Data',
    @subsystem = 'TSQL',
    @command = @Command,
    @on_success_action = 3,
    @retry_attempts = 5,
    @retry_interval = 5

set @Command = 'BACKUP LOG msdb TO DISK = ''' + @FileName + '.log_bak'''
-- Add job step (backup log).
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
    @step_name = 'Backup weighman Log',
    @subsystem = 'TSQL',
    @command = @Command,
    @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'

edit: DW, I don't need to backup the log database and that was causing the issue. Thanks again for your help