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

 

[RobP]

Something like:

--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 + '_msdb_' + CONVERT(varchar, getdate(), 112)
print @FileName

declare @Command nvarchar(max)

set @Command = 'BACKUP DATABASE msdb TO DISK = ''' + @FileName + '.dat_bak'''
-- Add job step (backup data).
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
    @step_name = 'Backup msdb 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 msdb 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'

you can change the format of the text by changing the number used in CONVERT (just check the help). You have to faff about with variables as you can't do the concatenation in the call to the stored procedure which is a bit annoying...

This will create a job with the current date, if you want to make it create a job which will set the date to when it was run you just tweak the @command string.

 

[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