Automate master database restore may fail or hang if a large number of databases exist in a SQL Server instance

Problem

Automate master database restore may fail or hang if a large number of databases exist in a SQL Server instance.

Error Message

V-79-57344-33938 - Database Query Failure. See the job log for details.

 

Solution

Automate master database restore may fail soon or hang up for one hour if a large number of databases exist in a SQL Server instance.  In a rare case, beremote crashed after the hang-up.

This issue can occur when a SQL Server instance has 1000 databases, but not limited to.

Cause:

This issue can occur in the following scenario.

1. To perform Automate master database restore, Backup Exec stops the SQL Server instance and starts it in Single user mode again.
2. The SQL Server instance takes a long time to start all databases in Single user mode.
3. Backup Exec waits a little and logs on to the instance successfully. However, the instance is not in stable condition to start all databases.
4. Backup Exec performs a restore SQL command and then the restore command fails immediately or hangs up for one hour.

Workaround:

Start the SQL Server instance in Single user mode and resubmit the restore operation as given below:

1. Manually restart the SQL Server instance in Single user mode.
Example: sqlservr.exe -c -m

2. Wait until all databases are started up and the instance is in stable condition.

3. Modify the SQL properties of the restore job as follows:
- Deselect the option "Automate master database restore" from the Restore Job properties. (Figure 1)
 
Figure 1
 
 
- Select "Overwrite the existing database" from Tools|Options|Job Defaults|Microsoft SQL. (Figure 2)
 
Figure 2
 

4. Run the restore job


 

 

 

Terms of use for this information are found in Legal Notices.

Search

Survey

Did this article answer your question or resolve your issue?

No
Yes

Did this article save you the trouble of contacting technical support?

No
Yes

How can we make this article more helpful?

Email Address (Optional)