Avamar: SQL restore failing with "Database is already open and can only have one user at a time"

Summary: Knowledge base (KB) article that provides a solution to SQL database restore failure when users access the database.

This article applies to This article does not apply to This article is not tied to any specific product. Not all product versions are identified in this article.
Check out other resources

Symptoms

SQL database restore to the original location is failing with errors similar to the following display:

avsql Error <40258>: sqlconnectimpl_smo::get_database_data_names Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Database 'Avamar1' is already open and can only have one user at a time.

 

Cause

One or more users or connections are accessing the database that is being restored to. The original database cannot be overwritten if there are users or connections accessing the database.

Resolution

Change the status of the database from MultiUser to Single-User mode.

Using SQL Server Management Studio

To set a database to single-user mode:

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  2. Right-click the database to change, and then click Properties.

  3. In the Database Properties dialog box, click the Options page.

  4. From the Restrict Access option, select Single.

  5. If other users are connected to the database, an Open Connections message appears. To change the property and close all other connections, click Yes.

 

Using Transact-SQL

To set a database to single-user mode:

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click the "Execute" button. This example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of the Avamar1 database to READ_ONLY and returns access to the database to all users. The termination option WITH ROLLBACK IMMEDIATE is specified in the first ALTER DATABASE statement. This causes all incomplete transactions to be rolled back and any other connections to the Avamar1 database to be immediately disconnected.

 

SQLCopy

USE master; GO ALTER DATABASE Avamar1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE Avamar1 SET READ_ONLY; GO ALTER DATABASE Avamar1 SET MULTI_USER; GO

Reference:  https://docs.microsoft.com/en-us/sql/relational-databases/databases/set-a-database-to-single-user-mode?view=sql-server-2017

Once that is done, retry the SQL database restore to the original location.

Affected Products

Avamar

Products

Avamar, Avamar Plug-in for SQL
Article Properties
Article Number: 000038587
Article Type: Solution
Last Modified: 11 Dec 2025
Version:  5
Find answers to your questions from other Dell users
Support Services
Check if your device is covered by Support Services.