Error 5123 in SQL Server 2008 R2 Database During Create File

Summary: If you are facing struggle with SQL Server 2008 R2 Database error 5123 during its execution, then you are at the right place. Whenever a user tries to create a new database file from a remote location, the user faces this error code. In this blog, we will provide all the best solutions to fix a permission error by using the manual method. For the quick and easy solution, we will also discuss the automated method using the MSSQL Recovery tool. Download Now   Purchase Now

There are two files used by SQL database, one is MDF and another is LDF. Where MDF is the primary file that contains schema and data, on the other hand, LDF stores logs. Whenever a user tries to attach these files to the server, an error takes place. So in this article, you will go through all the reasons and ways to fix this blunder in SQL server 2008 R2 Database.

Error Message

Whenever the error takes place the user gets a dialog box like this, notifying about the issue:

Reasons for the Error 5123

• Whenever a user tries to attach a database located in a different location and he/she doesn’t have enough privileges to that folder directory, they encounter it.
• If there are multiple user logins are involved to attach and detach a database then a user can encounter this error.
• If there is no permission is granted to run a particular program folder when a process is already getting executed on the server.

In such a situation the best way is to change the permission option or try to create a new permission option as a new owner of the MDF and LDF file.

How to Fix SQL Server Error 5123

There are multiple manual solutions available to fix this error. To resolve this issue, you can use any of the methods:

Method 1: Run As Administrator

• Go to the MDF location and right-click on the MDF file to be attached.
• Click on the properties and look for the file permission.
• To provide full control, click on the Add button and provide the login permission.

The same goes with the LDF file, to attach it to the server, follow the above method. You can directly Run As Administrator rather than granting permissions to each file. By doing so, you can easily fix SQL error 5123 in the Server 2008 R2 database.

Method 2: Using T-SQL Script

Using a stored procedure, we can attach a database to the SQL server. Below is a syntax to attach a database: sp_attach_single_file_db [@db_name=] ‘db_name’, [@physname=] ‘physical_name [@db_name =] ‘db_name’ is the database name. ‘db_name’ is nvarchar (128). [@physname =] ‘phsyical_name’ is the database file name. ‘phsyical_name’ is nvarchar (260). Example: EXEC sp_attach_db @name = ‘employer’ @filename1 = ‘C:\MSSQL\Data\employer.mdf @filename2 = ‘C:\MSSQL\Data\employer_log.ldf

Method 3: Using SQL Server Management Studio

• Launch SQL Management Studio and connect to the server using the login credentials or Windows authentication.
• Click on the Object Explorer and select the database that you want to attach.
• Now, click on the Attach option from the dropdown menu.
• A new window will appear and then click on the database that you want to attach to the server.
• Review the log file and then click on the OK button and sum up the whole process.

By following the above method, you can attach the database file to the SQL server and can easily fix Error 5123.

Method 4: Using MSSQL Server Repair Utility

SQL Repair is a utility that lets you review the whole database transactions and then it saves all the transactions as a new database file or as a SQL script. It takes an MDF file as input and generates the preview of the SQL database. Later you can export the queries like INSERT, UPDATE, and DELETE in a new database or script with all the constraints. It exports the newly created database automatically. Windows authentication can be used to login to the server or you can manually provide the user login credentials. Then all the work is done by the utility itself. To use this utility, follow the below steps:

• Launch the tool then add the MDF file path and define the recovery mode, then hit the OK button.
• Take a preview of all the transactions done over the database.
• Select the database items to export.
• Then click on the Save button.
• Select the saving option either “Save as New Database” or “Save as SQL Script“, then click on the OK button.
• Wait for some time and soon the new database will get attached to the server.

Conclusion

By following the above process, you will end up creating a new database same as the old one and then the utility will automatically attach the database to the SQL server. This way you can fix SQL error 5123 in server 2008 R2. You can directly download the tool from the above link and can easily fix the issue.

Related Post