Solve SQL Server error 4064

Linux

When the database a client is trying to work with is not available in the process of connection by the user, an SQL Server error 4064 occurs.

We have helped our customers to solve SQL related issues regularly as part of our Server Support Services.

In this context, We shall discuss about the nature of this error and how to solve it.

Why does SQL Server error 4064 happen?

The main reason why this error occurs is when a user of a database cannot access it due the unavailability of the database itself. In such a situation, a connection will not be able to take place and thus an Error 4064 will be triggered.
The database could have been dropped and therefore not available to the user.

How to solve SQL Server error 4064

Fixing this issue requires a logical approach. In that, a valid database needs to be available to enable the connection with the user to be possible.

We can fix this error by applying various means which we will discuss below.

By using SQLcmd utility

To use the sqlcmd tool, follow the following process;

i. From the Start bar, click on Run, enter cmd in the search bar and then press ENTER.

ii. On the command prompt, use the following command;

sqlcmd -S Name_of_Instance -d master -U SQL_Login -P Password

The Name_of_Instance in this case is the name of the SQL Server Instance you are are about working with while SQL_Login represents the SQL Server login of the deleted database. The Password specified in the command represents the password of the SQL Server login.

iii. Next, using the sqlcmd prompt, enter the following and press enter;

ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailableDBName

In the above command, the database is represented by AvailableDBName.

iv. Finally, enter GO followed by the Enter button in the sqlcmd to finalize the process.

SQL Server Management Studio Method

An easy method of resolving Server error 4064 via the SQL Server Management Studio is provided below;

To begin, open your SQL Studio and click on the connect to database where you can enter the name of server and other login information.
Next select the Options tab where you will see that the option to Connect to Database is set to the default database.
From here, you can select the available database to enable connection.