Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Liverpool, England
    Posts
    18

    Angry Unanswered: 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



  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Problems With Automated Jobs

    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.

  3. #3
    Join Date
    Aug 2003
    Location
    Liverpool, England
    Posts
    18
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •