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:

Backup ?

I using the latest and greatest sp on sql2k. I had a failure the other day
on one of our production servers and the guy that used to maintain is gone.
Here is the strategy I am thinking of
Full Recovery Model,
3am - Backup the Db and transaction log
7am - first shift arrives
8am - first differential backup (runs every hour on the hour)
8:30am - first transaction log backup (runs everyhour on the half hour)
This will run all day until 2nd shift leaves at around midnight.
What I want to know is this, I look at the differential backup and see a
name for the last full backup preformed. When this differential backup runs
tomorrow morning will it be hitting the newly created 3am backup or the one
from yesterday when I created the differential backup.
Thanks,
jc
John,
I have some difficulty following you. Especially below part:

> What I want to know is this, I look at the differential backup and see a
> name for the last full backup preformed. When this differential backup runs
> tomorrow morning will it be hitting the newly created 3am backup or the one
> from yesterday when I created the differential backup.
I'm not sure where you "see" the name of the full backup. Also, what do you mean by "hitting the newly created
3am backup". Perhaps you can rephrase or explain a bit further. Or even post some BACKUP and/or RESTORE
commands?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message news:uSwuaWvJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> I using the latest and greatest sp on sql2k. I had a failure the other day
> on one of our production servers and the guy that used to maintain is gone.
> Here is the strategy I am thinking of
> Full Recovery Model,
> 3am - Backup the Db and transaction log
> 7am - first shift arrives
> 8am - first differential backup (runs every hour on the hour)
> 8:30am - first transaction log backup (runs everyhour on the half hour)
> This will run all day until 2nd shift leaves at around midnight.
> What I want to know is this, I look at the differential backup and see a
> name for the last full backup preformed. When this differential backup runs
> tomorrow morning will it be hitting the newly created 3am backup or the one
> from yesterday when I created the differential backup.
> Thanks,
> jc
>

Backup ?

I using the latest and greatest sp on sql2k. I had a failure the other day
on one of our production servers and the guy that used to maintain is gone.
Here is the strategy I am thinking of
Full Recovery Model,
3am - Backup the Db and transaction log
7am - first shift arrives
8am - first differential backup (runs every hour on the hour)
8:30am - first transaction log backup (runs everyhour on the half hour)
This will run all day until 2nd shift leaves at around midnight.
What I want to know is this, I look at the differential backup and see a
name for the last full backup preformed. When this differential backup runs
tomorrow morning will it be hitting the newly created 3am backup or the one
from yesterday when I created the differential backup.
Thanks,
jcJohn,
I have some difficulty following you. Especially below part:
> What I want to know is this, I look at the differential backup and see a
> name for the last full backup preformed. When this differential backup runs
> tomorrow morning will it be hitting the newly created 3am backup or the one
> from yesterday when I created the differential backup.
I'm not sure where you "see" the name of the full backup. Also, what do you mean by "hitting the newly created
3am backup". Perhaps you can rephrase or explain a bit further. Or even post some BACKUP and/or RESTORE
commands?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message news:uSwuaWvJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> I using the latest and greatest sp on sql2k. I had a failure the other day
> on one of our production servers and the guy that used to maintain is gone.
> Here is the strategy I am thinking of
> Full Recovery Model,
> 3am - Backup the Db and transaction log
> 7am - first shift arrives
> 8am - first differential backup (runs every hour on the hour)
> 8:30am - first transaction log backup (runs everyhour on the half hour)
> This will run all day until 2nd shift leaves at around midnight.
> What I want to know is this, I look at the differential backup and see a
> name for the last full backup preformed. When this differential backup runs
> tomorrow morning will it be hitting the newly created 3am backup or the one
> from yesterday when I created the differential backup.
> Thanks,
> jc
>

Backup ?

I using the latest and greatest sp on sql2k. I had a failure the other day
on one of our production servers and the guy that used to maintain is gone.
Here is the strategy I am thinking of
Full Recovery Model,
3am - Backup the Db and transaction log
7am - first shift arrives
8am - first differential backup (runs every hour on the hour)
8:30am - first transaction log backup (runs everyhour on the half hour)
This will run all day until 2nd shift leaves at around midnight.
What I want to know is this, I look at the differential backup and see a
name for the last full backup preformed. When this differential backup runs
tomorrow morning will it be hitting the newly created 3am backup or the one
from yesterday when I created the differential backup.
Thanks,
jcJohn,
I have some difficulty following you. Especially below part:

> What I want to know is this, I look at the differential backup and see a
> name for the last full backup preformed. When this differential backup run
s
> tomorrow morning will it be hitting the newly created 3am backup or the on
e
> from yesterday when I created the differential backup.
I'm not sure where you "see" the name of the full backup. Also, what do you
mean by "hitting the newly created
3am backup". Perhaps you can rephrase or explain a bit further. Or even post
some BACKUP and/or RESTORE
commands?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message news:uSwuaWvJEHA.3592@.TK2MSFTNGP09
.phx.gbl...
> I using the latest and greatest sp on sql2k. I had a failure the other day
> on one of our production servers and the guy that used to maintain is gone
.
> Here is the strategy I am thinking of
> Full Recovery Model,
> 3am - Backup the Db and transaction log
> 7am - first shift arrives
> 8am - first differential backup (runs every hour on the hour)
> 8:30am - first transaction log backup (runs everyhour on the half hour)
> This will run all day until 2nd shift leaves at around midnight.
> What I want to know is this, I look at the differential backup and see a
> name for the last full backup preformed. When this differential backup run
s
> tomorrow morning will it be hitting the newly created 3am backup or the on
e
> from yesterday when I created the differential backup.
> Thanks,
> jc
>