Backup and Restore

I have a backup of all my databases schedule to run every night but the problem is that I have to take the latest backup of database A and restore it into database B every morning. I have been doing this every morning manually but is there anyway by which I can schedule this to run automatically every morning bearing in mind that I cannot do DTS to transfer data. Database A must not be touched. The restore to database B has to be from the backup of database A.
Please any response will be greatly appreciated.I have a backup of all my databases schedule to run every night but the problem is that I have to take the latest backup of database A and restore it into database B every morning. I have been doing this every morning manually but -

Q1 Is there anyway by which I can schedule this to run automatically every morning?

- bearing in mind that I cannot do DTS to transfer data. Database A must not be touched. The restore to database B has to be from the backup of database A. Please any response will be greatly appreciated.

A1 Yes. (Create a job with a backup step and a restore step scheduled to run each morning.

For example:

-- PubsDumpABackUp --> PubsBRestore Job Script
-- By: dba

BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'PubsDumpABackUp --> PubsBRestore') > 0
PRINT N'The job "PubsDumpABackUp --> PubsBRestore" already exists so will not be replaced.'
ELSE
BEGIN

-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'PubsDumpABackUp --> PubsBRestore', @.owner_login_name = N'sa', @.description = N'No description available.', @.category_name = N'Database Maintenance', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'Step 1', @.command = N'BACKUP DATABASE [Pubs]
TO DISK = ''C:\PubsDumpA.Bkp''
WITH INIT ,
NAME = ''PubsDumpABackUp'', SKIP , STATS = 1 --, FORMAT', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 4, @.retry_attempts = 0, @.retry_interval = 0, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 2, @.step_name = N'Step 2', @.command = N'RESTORE DATABASE [PubsB]
FROM
DISK = ''C:\PubsDumpA.Bkp''
WITH FILE = 1, NOUNLOAD,
STATS = 1, RECOVERY, REPLACE,
MOVE ''pubs''
TO ''C:\PubsB.mdf'',
MOVE ''pubs_log''
TO ''C:\PubsB_log.ldf''', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1

IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1, @.freq_type = 4, @.active_start_date = 20021025, @.active_start_time = 50000, @.freq_interval = 1, @.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval = 0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: -- PubsDumpABackUp --> PubsBRestore Job Script
-- By: dba

BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'PubsDumpABackUp --> PubsBRestore') > 0
PRINT N'The job "PubsDumpABackUp --> PubsBRestore" already exists so will not be replaced.'
ELSE
BEGIN

-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'PubsDumpABackUp --> PubsBRestore', @.owner_login_name = N'sa', @.description = N'No description available.', @.category_name = N'Database Maintenance', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'Step 1', @.command = N'BACKUP DATABASE [Pubs]
TO DISK = ''C:\PubsDumpA.Bkp''
WITH INIT ,
NAME = ''PubsDumpABackUp'', SKIP , STATS = 1 --, FORMAT', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 4, @.retry_attempts = 0, @.retry_interval = 0, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 2, @.step_name = N'Step 2', @.command = N'RESTORE DATABASE [PubsB]
FROM
DISK = ''C:\PubsDumpA.Bkp''
WITH FILE = 1, NOUNLOAD,
STATS = 1, RECOVERY, REPLACE,
MOVE ''pubs''
TO ''C:\PubsB.mdf'',
MOVE ''pubs_log''
TO ''C:\PubsB_log.ldf''', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1

IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1, @.freq_type = 4, @.active_start_date = 20021025, @.active_start_time = 50000, @.freq_interval = 1, @.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval = 0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: