Error 5123 in SQL Server 2008 R2 Database During Create File

If you are a SQL Server user then there is a big chance that you have encountered SQL Server 2008 R2 Database error 5123 during its execution. Whenever a user tries to create a new database file from a remote location S/He face this error code. It’s a permission error so there is no need to worry about and you can fix it very easily.

There are two files used by SQL database, one is MDF and another is LDF. Where MDF is the primary file which contains schema and data, on the other hand, LDF stores logs. Whenever a user tries to attach these files to the server, the 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.

Appearance

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 on 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 should try to create a new permission option as a new owner of MDF and LDF file.

---

You Might Like:

Fatal Error 823 in MS SQL Server Database

Fix MySQL Error 1146 Table doesn’t exist

---

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:

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 the full control, click on the Add button and provide the login permission.

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

Using T-SQL Script:

Using a stored procedure, we can attach a database in 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

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.

Using MSSQL Server Repair Utility:

SQL Repair is a utility which lets you review the whole database transactions and then it saves all the transaction as a new database file or as a SQL script. It takes 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 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.

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