Problems With Automated Jobs

Hi guys

I have setup a sequence of jobs to run maintenance on one of our SQL Servers.

The sequence of jobs is as follows:

Backup database
Archive database
Recreate indexes
Shrink database
Update statistics
Backup database

There is also another step which is Restore Database. This step is only called if the Archive, Re-index or Shrink jobs fail.

I made a slight script error and so when the job ran on Sunday it deemed the job a failure and attempted to restore the database, that wasn't a problem as the error was deliberate to test the restore process.

The restore process failed and logged the following message:

Job 'Sunday Maintenance - Full' : Step 8, 'Restore Database' : Began Executing 08/02/04 22:58:40

Msg 3101, Sev 16: Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000]
Msg 3013, Sev 16: Backup or restore operation terminating abnormally. [SQLSTATE 42000]

The syntax I used to do the restore was:

RESTORE DATABASE Emvolve
FROM DISK = 'H:\Dbdata\Backup\Emvolve_Full_Before.BAK'
WITH REPLACE

How do I get around this error message to complete the restore automatically? Any services that access this database are stopped prior to this process running so nothign should be accessing SQL.

Any ideas would be appreciated as I am only a learner as far as SQl goes.

Cheers

Paul

:)One of the reasons why automating a restore process is not a good idea is because all users will have to log off first before you can restore the database. Just imagine someone who may lose hours of work because a restore was performed. Restore, especially overwriting the existing database, should be carried out manually with caution unless you are restoring database backup to a test machine. Of course, you can write a script to kill all existing users on the database and achieve your goal but it's always dangerous to do it that way.|||Thanks for the reply

When these jobs are initiated there are other batch files wich are ran to disconnect any users from the system. There are no users using the system from the desktop end as the IIS is stopped prior to the maintenance jobs running.

Maybe I will remove the restore step and have this ran manually|||What database is the job running under? Probably the one you what to restore to...

Why do you want to restore?

I didn't catch that part...

I resotre a prod db nightly to dev for support...

Try:

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

from another database...before the restore...