Updated on September 14th, 2023
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.
Whenever the error takes place the user gets a dialog box like this, notifying about the issue:
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.
There are multiple manual solutions available to fix this error. To resolve this issue, you can use any of the methods:
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.
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
By following the above method, you can attach the database file to the SQL server and can easily fix Error 5123.
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:
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.
About The Author: