An error occurred when attaching the database s click the hyperlink

Hello Guys!

I just installed SQL Server 2008 R2 a couple of days ago, on the first day use i can attach database(.mdf) with out any problem.

on the day 2, i keep getting this error:

http://img515.imageshack.us/img515/14/1212i.png

after clicking the Hyperlink this window open:

http://img34.imageshack.us/img34/8766/hyperg.png

in that day i reinstall SQL Server 2008 R2, because I’m so confused. after that i try to attach i still got the same error, what i did is i share the folder where the database is.

i open SSMS and try to attach and i attached without any error…

i think this is the 4rth day, untill now everytime i want to attach a database i got this error, and even i try to share the folder where the database is i still got that error.

any advice guys?

Thanks a lot

jhules

---

if you want to be happy, find something you love to do and do it for free, then find someone to to you for it.

Introduction:

One of the common Errors while attaching a .mdf file for restoring Data is as follows:

An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details.

The detailed message is as follows.

Step 1:

Right, Click on the .mdf that you want to attach and click on Properties.

Step 2:

Go to Security tab and give Full Control to all Users and Groups by clicking on Edit.

Click on the Check Box under Allow section for Giving Full Control to all Users and Groups.

Step 3:

Right-click on the Main Folder containing your .mdf file and go to the properties section.

Deselect the  “Read Only Option” in “General Tab” and click on Apply.

Step 4:

Go to the “Security Tab” and give “Full Control” to all “Groups and User Names” as shown in Step 2 and click on Apply.

Select “Apply changes to this folder, subfolder and files” option and click on “Ok”.

Click on “Ok” to apply all the changes.

Step 5:

Now go to SQL and Attach your .mdf file Again and your DB will be attached to the Databases folder and you can access it.

Check out my other blog here https://www.cloudfronts.com/blog/dynamics-nav/performing-update-operation-in-microsoft-dynamics-nav-through-integration-in-tibco-cloud-integration/

How to resolve error when attaching a database in SQL Server

Introduction:

One of the common Errors while attaching a .mdf file for restoring Data is as follows:

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

The detailed message is as follows.

Step 1:

Right, Click on the .mdf that you want to attach and click on Properties.

Step 2:

Go to Security tab and give Full Control to all Users and Groups by clicking on Edit.

Click on the Check Box under Allow section for Giving Full Control to all Users and Groups.

Step 3:

Right-click on the Main Folder containing your .mdf file and go to the properties section.

Deselect the “Read Only Option” in “General Tab” and click on Apply.

Step 4:

Go to the “Security Tab” and give “Full Control” to all “Groups and User Names” as shown in Step 2 and click on Apply.

Select “Apply changes to this folder, subfolder and files” option and click on “Ok”.

Click on “Ok” to apply all the changes.

Step 5:

Now go to SQL and Attach your .mdf file Again and your DB will be attached to the Databases folder and you can access it.

Источник

An error occurred when attaching the database sql server

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I just installed SQL Server 2008 R2 a couple of days ago, on the first day use i can attach database(.mdf) with out any problem.

on the day 2, i keep getting this error:

TITLE: Microsoft SQL Server Management Studio
——————————

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

after clicking the Hyperlink this window open:

TITLE: Microsoft SQL Server Management Studio
——————————

Attach database failed for Server ‘STARGAZER-PCSQLSTARMAX’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Unable to open the physical file «E:New folderDataBase.MDF». Operating system error 5: «5(Access is denied.)». (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

in that day i reinstall SQL Server 2008 R2, because I’m so confused. after that i try to attach i still got the same error, what i did is i share the folder where the database is.

i open SSMS and try to attach and i attached without any error.

i think this is the 4rth day, untill now everytime i want to attach a database i got this error, and even i try to share the folder where the database is i still got that error.

any advice guys?

if you want to be happy, find something you love to do and do it for free, then find someone to to you for it.

Источник

An error occurred when attaching the database sql server

Вопрос

I just installed SQL Server 2008 R2 a couple of days ago, on the first day use i can attach database(.mdf) with out any problem.

on the day 2, i keep getting this error:

TITLE: Microsoft SQL Server Management Studio
——————————

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

after clicking the Hyperlink this window open:

TITLE: Microsoft SQL Server Management Studio
——————————

Attach database failed for Server ‘STARGAZER-PCSQLSTARMAX’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Unable to open the physical file «E:New folderDataBase.MDF». Operating system error 5: «5(Access is denied.)». (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

in that day i reinstall SQL Server 2008 R2, because I’m so confused. after that i try to attach i still got the same error, what i did is i share the folder where the database is.

i open SSMS and try to attach and i attached without any error.

i think this is the 4rth day, untill now everytime i want to attach a database i got this error, and even i try to share the folder where the database is i still got that error.

any advice guys?

if you want to be happy, find something you love to do and do it for free, then find someone to to you for it.

Источник

An error occurred when attaching the database sql server

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I just installed SQL Server 2008 R2 a couple of days ago, on the first day use i can attach database(.mdf) with out any problem.

on the day 2, i keep getting this error:

TITLE: Microsoft SQL Server Management Studio
——————————

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

after clicking the Hyperlink this window open:

TITLE: Microsoft SQL Server Management Studio
——————————

Attach database failed for Server ‘STARGAZER-PCSQLSTARMAX’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Unable to open the physical file «E:New folderDataBase.MDF». Operating system error 5: «5(Access is denied.)». (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

in that day i reinstall SQL Server 2008 R2, because I’m so confused. after that i try to attach i still got the same error, what i did is i share the folder where the database is.

i open SSMS and try to attach and i attached without any error.

i think this is the 4rth day, untill now everytime i want to attach a database i got this error, and even i try to share the folder where the database is i still got that error.

any advice guys?

if you want to be happy, find something you love to do and do it for free, then find someone to to you for it.

Источник

An error occurred when attaching the database sql server

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I’m told by my website host support that in order to upload the database portion of my ASP.NET website created with Visual Studio 2017 I need to make a script of the local mdf database in SSMS, then restore it to a new database that I have added to the remote server. My problem is that I am having difficulty attaching the local mdf database to SSMS. I was able to find and connect to my local sqlexpress server, but when attempting to «attach» the database in Object Explorer was unable to «see» my local user directory that contained the mdf database. I then used sql server configuration manager to change the sqlexpress build in account login to «local system», as described in:

I was then able to find and select the local mdf database. However, when I try to «attach», I get the error:»An error occurred when attaching the database(s). Click the hyperlink in the message for details». But there is no hyperlink.

Any help would be greatly appreciated!

Answers

I dont have knowledge on Visual Studio so cant say whether that will cause issues. But the SQL Configuration Manager side, i can help with. You will have a separate SQL COnfiguration Manager option for each version of SQL you have installed. it will be blank if you’re and old one that has been upgraded. Find the new one and you should see options to set the port.

When you say you have the MDF locally, do you mean its on your local machine? To attach an MDF, the file should be on the SQL servers local drive as it will need to have it locally to itself, not on your local machine. Or have I misunderstand?

SQL Express comes with Visual Studio 2017, and both are installed on my local desktop computer, as is SSMS. I could not «see» the mdf file from SSMS until I used sql server configuration manager as described. The mdf file is in the AppData folder of the Visual Studio project, and I can now see it in the file picker that comes up in SSMS «attach». But is there more to be done to allow attachment? Or could I copy the database to another location where the copy could be attached? I’m puzzled that there was no information about the nature of the error.

I do not have direct answer to your problem but it seems like some security thing which does not allows you. What you could do is copy the mdf file to location

drive:Program FilesMicrosoft SQL ServerMSSQLn.SQLEXPRESSMSSQLBinn

n =11,12,13,14 depending on version of sql server. Close the SSMS and launch it as administrator and hopefully you should be able to see now

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

Thanks for the suggestion, Shashank. I tried what you suggested. Unfortunately I still get the same uninformative error message as before.

PS: I want to attach an image of what it looks like, but I always get the message: Body text cannot contain images or links until we are able to verify your account. Who knows when that will be?

If you’re on SQL Express then you’re limited to the DB size. I dont know what error that would give if you tried to attach it but is it over 10GB in size?

Thanks Matt. The DB size is only a little over 3 MB. The website is in development, and the DB only contains test data at this point. BTW, when I connect to SQLEXPRESS in SSMS, I select Windows Authentication, as that is what I use in Visual Studio. Could that be a factor?

No Windows auth is fine. The attachment is done by the SQL service so that needs full access to the MDF file. Do me a favour though and move the MDF from the binn folder to the DATA one in the same location.

I thought he asked you to put it in DATA, so if you havent then try that.

The only other time I can remember having issues attaching MDFs is when it was going to a version older than it was originally on. But if I remember correctly, the error for that is obvious. maybe not on Express though. Do you know if the version of SQL this came from was the same year or older than the one you’re trying to attach it to?

Use T-SQL command to attach the database, so that you get all the error messages:

CREATE DATABASE mydb ON (NAME = ‘mydb’, FILENAME = ‘

.mdf’),
(NAME = ‘mydb_log’, FILENAME = ‘

mydb should be the name you used in Visual Studio.

should be whereever you have the files now. (But preferrably, they should be in the DATA directory.)

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Thanks for your suggestion, it has moved things along, but I’m not quite there yet. After running the T-SQL you suggested, I got the message:

«Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘LabEle3’. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database ‘LabEle3’ cannot be opened because it is version 782. This server supports version 663 and earlier. A downgrade path is not supported.»

I discovered that my SSMS-visible version of SQLEXPRESS was 10.50.4042, while the Visual Studio Version of SQL Server was 12.00.2000. It did not occur to me that these would be separate instances.

So: I downloaded and installed the latest version of SQL Server Express (2017), which installed as SQLEXPRESS01, along with the latest version of SSMS (15.0.18118.0, run as administrator from under SQL Server Tools 18 from the Start button). However, on trying to connect to SQLEXPRESS01 in the updated SSMS, I get the error message:

Cannot connect to XPS-DESKTOPSQLEXPRESS01.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 — Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

The system cannot find the file specified

The Help links to an offer to buy a Surface laptop.

Both SQLEXPRESS (the old version) and SQLEXPRESS01 (the new version) are listed in the Registered Servers. Since the 01 version was just installed, any ideas on why I might not be able to connect to it?

Источник

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

While attaching .mdf and .ldf database files in SQL server management studio, it throws error attached image – 1

Image – 1

It seems permission issue for which it gives this error while attaching the .mdf and .ldf file. so to make it work, we need to give permission to the folder containing the database (.mdf, .ldf). else directly to the database files.

As shown in Image – 2. it does not have full control for which it throws error while attaching database.

Image – 2

Now I have given Full Control access to the Users for those .mdf and .ldf file as shown in Image – 3

Image – 3

Now do the same process to attach the .mdf file by right clicking on database and locate the .mdf file to restore.

This time database will get attached successfully without giving any error.

Another way you to overcome this error

while attaching the .mdf file is to stop the SQL Server service first

To START and STOP SQL Server Service

Go to run type services.msc once that page opens search for SQL Server Service 
and STOP it from the left panel. Same process to START the SQL Server Service

and then put the .mdf and .ldf inside Data folder where SQL Server was installed and then restart the SQL Server service, then attach the .mdf file. it wont give permission error.

You might also like:

How to truncate a SQL Server Data file

How to Backup a Database using SQL Management Studio

How to Restore Database using SQL Management Studio

Failed to connect to MySQL at with user root cannot connect to MySQL server on (10060)

Recently I was going through a database programming tutorial that used Northwind Database in code examples, so I thought «No problem, I’ll quickly add that database to my SQL Server 2012». I assumed this would be easy but to my surprise that wasn’t the case.

In this post, I will show you what problems I encountered while trying to install the Northwind database and how I finally managed to make it work.

Note:Solution below was tested with the following configuration:

• Windows 7 Professional 64 bit
• SQL Server 2012 Express Edition

Step 1 — Getting Northwind Database and examining its content

First we need to download the Northwind Database. Save the file on your computer and run the installer (SQL2000SampleDb.msi). Installation Wizard will appear. Go through all the steps and in the end it should install the files in the following folder:

c:SQL Server 2000 Sample Databases

The folder will contain files for two databases:

1. Pubs sample database
   (contains information about a book publishing company)
2. Northwind sample database
   (contains sales data from a trading company)

Each sample database will have 3 files each with different extensions:

• .sql
  This one contains script for generating the database. After database is created, the data is stored in MDF and LDF files.
• .MDF
  This is a primary data file where schema and data of the database is stored.
• .LDF
  This is a log file where a transaction log information is stored. It keeps track of everything done to the database and can be used to rollback or restore the database.

Note:Besides Northwind database, there is also AdventureWorks database. If you would like to give AdventureWorks a try, you can download it from GitHub.

Step 2 — Installing it on a SQL Server

As already mentioned in the previous step, the MDF file is where data of the database is located and this file can be used to attach the database to the server, so this is the first thing I tried.

Failed Attempt #1 — attaching database using SQL Server Management Studio (SSMS)

This tool is available when you install SQL Server 2012. Using Windows 7 you can easily run it by typing sql management in the Start Menu search field.

Tip:If you are don’t have this tool, you can obtain it from here.

When I tried to attach NORTHWND.MDF file, Error Window appeared with following error:

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

Failed Attempt #2 — attaching database using Visual Studio Express

Next, I tried to attach it using VS Express. This time I received this warning:

The database file that you are attempting to connect to is not compatible with the current instance of SQL Server. To continue, you must upgrade this database file….Do you want to upgrade the database file now?

I clicked Yes hoping the upgrade will work, but I received another error instead described next.

Errors received with attempt #1 and #2

In both cases, I got the following error when attempting to attach the NORTHWND.MDF file:

Unable to open the physical file C:SQL Server 2000 Sample DatabasesNORTHWND.MDF … Operating system error 5 (Access is denied), Microsoft SQL Server Error 5120.

I tried to solve this problem by running those applications as an administrator (right-clicking on the application and selecting Run as administrator) but it didn’t do much except giving me another error:

Database C:SQL Server 2000 Sample DatabasesNORTHWND.MDF cannot be upgraded because its non-release version (539) is not supported by this version of SQL server…CREATE DATABASE is aborted.

So instead of attaching the database I decided to try to execute instnwnd.sql file.

This attempt was successful and is described in the next section.

What finally worked — executing SQL script in instnwnd.sql file

To execute the script I used SQL Server Management Studio 2012 (SSMS) tool that got installed with SQL Server Express. The original script also gave me errors but after a little search on the web, I found a few different suggestions on what to modify in the script, one of which worked.

To successfully execute the instnwnd.sql script, run the SQL Server Management Studio and try these steps:

1. When the SSMS is loaded, Connect to Server window will appear. Click on Connect button. If you are unable to connect to the SQL Server, maybe you are not using Windows Authentication. If that’s the case, select SQL Server Authentication  for Authentication and provide the necessary username and password. After successful connection, your SQL Server instance should be listed in the Object Explorer.
2. Now under File Menu select Open > File.
3. Find the instnwnd.sql in your computer and select it. Click Open
4. SQL script should open in the main window.
5. Do not click on Execute yet. If you do, you will get the following error:

   Could not find stored procedure ‘sp_dboption’.

6. Around the line 20, remove the following two lines:

   exec sp_dboption 'Northwind','trunc. log on chkpt.','true'
   exec sp_dboption 'Northwind','select into/bulkcopy','true'
   

7. Replace them with this line as shown below:

   alter database Northwind set recovery simple
   

   

   Click image to enlarge

8. All that is left is to execute the script, so click on Execute.

After following the above steps you should now have the Northwind database on your SQL Server.

Conclusion

With the Northwind database, you cannot simply attach it or execute its original .sql script on newer versions of SQL servers like 2012 or even 2008. This article demonstrated how to successfully execute instnwnd.sql file with just a small modification in the script.

I hope you have found this post useful. Feel free to drop a comment or share this article.

Возникает ошибка в SQL Server Management Studio 18 при подключении демонстрационной базы данных данных Northwnd, от Microsoft. SQL Server Management Studio запускаю от имени администратора, возникает следующая ошибка видимо проблема с файлом NORTHWND_log.ldf

это после нажатия ОК

“TITLE: Microsoft SQL Server Management Studio An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.”

и разворачиваем подробно

“TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server ‘USER-PKSQLEXPRESS’. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlin… nkId=20476

—————————— ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Unable to open the physical file «E:SQL_BobrovskNORTHWND_log.ldf». Operating system error 2: «2(Не удается найти указанный файл. Cannot find the specified file..)». (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink… kId=20476”

в чем может быть проблема подскажите? Заранее спасибо

How to Move a SQL Server Database

Today I ran into a problem with my C: drive running out of space. It’s an older SSD drive that I purchased about 5 years ago, and only has a 256 GB capacity. Over time it has accumulated a lot of software and some data. I also have a 1TB HDD E: drive (the D: drive is my CD drive, yes I have an internal CD ROM drive!). I have SQL Server installed along with some large databases all stored on the C: drive in the default path:

C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATA

An SSD drive performs best if it have about 25% free space or more. I had about 24MB free, and I wasn’t able to perform OS updates, or other software updates anymore. In this post I describe the steps that I followed to move my multi-gigabyte SQL Server databases to the E: drive.

To see where your databases are stored on disk, in SQL Management Studio (SSMS), select the database, right click and select ‘properties’. in properties, choose file and you will see the following details about the selected database.

Close Database Connections

In my case the databases I moved are used by a web application. In IIS I stopped the application pool and the site that use the databases. If you don’t have access to the application connecting to your database you can set it to single user mode and force the remote connections to close. Keep in mind that this will cause a service outage and might cause transaction rollbacks if there are in progress data updates.

Detach the Database

In order to host the databases from an alternative location to the default I had to detach them. Backup and Restore or creating a Bacpac file allows you to move databases from one Database Server to another (like moving from one environment to another).

To detach a SQL Server database, in SSMS right click on the database and select ‘Tasks’ -> ‘Detach…’. This will open the following screen:

• Drop Connections — This puts the database into single user mode and forces existing connections to close. I had already closed application connections turning off the web app that used the database, but I selected this in case there were still open connections.
• Update Statistics — Select this option to update the optimization statistics. I believe it is a good practice to enable this option when moving databases to new infrastructure, as I did in my scenario (from SSD to HDD).

Once the detach operation is complete, the database should no longer be visible in SSMS. The MDF and LDF files can be moved to the new location. I did a file copy to an exactly parallel location on my E: drive.

E:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATA

You can of course choose any place to store the MDF and LDF files.

Attach the moved Database

Once the database files had been moved, from SQL Studio I attempted to attach the database. From SSMS, right click on ‘Databases’ to display the context menu and select ‘attach…’. The attach screen is displayed. Click ‘Add…» to select the newly moved MDF file. In the screen shot below I have selected my example database file from its new home on the E: drive.

However, when I click «OK» to re-attach my database, I get this obscure error.

«An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details.»

The hyperlink in the Message column can be found on the ‘Attach Databases’ screen as the far right column.

 

Clicking on this link displays an obscure message.  «Unable to open the physical file… Operating System error 5: Access is Denied.

 

Fortunately, this is easily resolved. The MDF and LDF files I copied cannot be accessed by the account I using to log into SQL Server.  By adding «Full Control» access to the MDF and LDF files through the file manager security app for my local account I was able to resolve this error.

The local account I am using is called «root», so I have granted full control to that account for both the MDF and LDF files. These are the permissions on the source MDF and LDF files. So the other permissions that are added with the file copy can be removed, in keeping with the ‘least privelege’ security practice.

Verify the Attached Database

Once the permission problem was cleared up, the database attachment was successful. The example_database now appears in SSMS and if I right click and view it’s file properties I can see it is being served from the E: drive.

 

Success! I restart the application pool and IIS site and the database is working as expected.

I can now remove the database files from my C: drive and free up valuable space. Don’t forget to empty the recycle bin.

Summary

The simplest way to move a SQL Server database is to detach, move and attach it. Just watch for file permission issues that may happen as a result of the file copy. My example is very specific to my setup. I used integrated security with a local admin account instead of a SQL account. I am not sure if the SA account would run into a file permission error like I did.

Sources Cited

Microsoft has some good information at these links:

• Detach a Database
• Attach a Database

Photo by Elevate on Unsplash