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