STATUS CODE: 5 - Attempts to restore a SQL database fail with a VERITAS NetBackup (tm) Exit Status Code 5: the restore failed to recover the requested files. Additionally, the NetBackup SQL View Status shows the following…

Overview
Attempts to restore a SQL database fail with a VERITAS NetBackup (tm) Exit Status Code 5, " the restore failed to recover the requested files".  Additionally, the NetBackup SQL View Status shows  the message, "Exclusive access could not be obtained because the database is in use".

Troubleshooting
In general, the error "Exclusive access could not be obtained because the database is in use" indicates either a person or a program has a connection open to the database being restored.  Generally, this connection is SQL Enterprise Manager.  If SQL Enterprise Manager is open, this will create a connection to the database, and cause the restore to fail with this error.  In this case, however, SQL Enterprise Manager is not open.  A review of the accounts used in the backup and restore of SQL databases indicates the source of the problem.

There are two places within NetBackup where accounts are set for use with backups and restores of SQL databases.  The first is the NetBackup Client Service, see Figure 1.

Figure 1
 

Generally, this is the same account that is used for the NetBackup SQL graphical user interface (GUI) to handle backup and restore operations.  See Figure 2.

Figure 2
 

If this account is set within SQL server to use a database other than master as the default database (say, for example, pubs), any attempt to restore the default database (pubs) results in the "database in use" error message.  

Master Log Files:  N/A
Media Server Log Files:  N/A

Client Log Files:
In the dbclient log file, the following error message is observed:

13:10:52.953 [2532.428] <16> CODBCaccess::LogODBCerr: DBMS MSG - ODBC return code <-1>, SQL State <37000>, SQL Message <3101><[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be obtained because the database is in use.>.
13:10:52.953 [2532.428] <16> CODBCaccess::LogODBCerr: DBMS MSG - SQL Message <3013><[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is terminating abnormally.>
13:10:52.953 [2532.428] <16> Dbbackrec::PerformNBOperation: ERR - Error found executing <restore database "pubs" from VIRTUAL_DEVICE='VNBU0-2532-428-1129745152' with blocksize = 65536, maxtransfersize = 65536, buffercount = 1, replace , norecovery>.

Resolution
A) To change the default database associated with a SQL user, open SQL Enterprise Manager, and within the SQL Server Group select the correct SQL server, then select Security, and then Logins.  See Figure 3.

Figure 3
 

Then, double-click on the user ID, and on the General page, set the default database to master, see Figure 4.

Figure 4
 


If the problem persists after following the above listed resolution (Resolution A), please try the steps below Resolution (B):

B) Log in to the SQL Management studio (SQL 2005) and open a query window or the Query analyzer (SQL 2000) and run the following stored procedure:

sp_who2

The sp_who2 internal procedure allows users to view current activity on the databases on that instance. This command provides a view into several system tables (e.g., syslocks, sysprocesses, etc.). The sp_who2 command returns the following information:

   * Spid—The system process ID.
   * status—The status of the process (e.g., RUNNABLE, SLEEPING).
   * loginame—Login name of the user.
   * hostname—Machine name of the user.
   * blk—If the process is getting blocked, this value is the SPID of the blocking process.
   * dbname—Name of database the process is using.
   * Cmd—The command currently being executed (e.g., SELECT, INSERT)
   * CPUTime—Total CPU time the process has taken.
   * DiskIO—Total amount of disk reads for the process.
   * LastBatch—Last time a client called a procedure or executed a query.
   * ProgramName—Application that has initiated the connection (e.g., Visual Basic, MS SQL Query Analyzer)

The dbname column will list all the databases which have a connection established to them. Please make sure that the database being restored does not feature in this list. If the database in question is listed there, it means that there are still connections established with that database (possible "rogue" connections). If required, the kill <spid> command can be used to kill the connection to that database. The other column that needs to be looked at is the blk (blkby in SQL 2005) column, which shows if a process is being blocked by another process. The kill <spid> command needs to be used carefully as it will not commit any unfinished transactions by that connection to the database.

Following the steps above should resolve this issue.