0

I am not able to attach a database to a SQL Server 2008 on different machine. I moved the .mdf and .ldf files after detaching the database from one computer to another. When I try to attach the database on new machine the database does not show up on the file location.

If I browse manually I can see the files (.mdf and .ldf). There are no hidden files under the MSSQL > DATA folder which contains all the other database files.

Also is there a way to backup the database, move it to new machine and add under SQL Server? If yes then how can I do so? Please advise.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2149838
  • 49
  • 1
  • 1
  • 8
  • 1
    Yes, backup & restore would be much easier - and there is ***TONS*** of **freely available**, excellent documentation on all of this - e.g. [Backup Overview (SQL Server)](http://technet.microsoft.com/en-us/library/ms175477.aspx) - please do go read it yourself - it's there, just go read it! – marc_s Mar 21 '14 at 15:59

1 Answers1

2

If you see the MDF and LDF files via file explorer, but not in SQL Server Management Studio, your SQL Server Management Studio login might have insufficient permissions

If the SQL Server version is the same for both original and destination instance, there should be no problems to attach the MDF and LDF files

  1. Right-click the Databases node in Object Explorer
  2. Select Attach...

enter image description here

3.Click Add

enter image description here

4.Navigate to the folder the MDF and LDF files are stored. make sure the SSMS login has enough privileges for the files/folders

enter image description here

5.Select the MDF file and click OK

The patch for the LDF file will be automatically added if it's in the same folder. If not, add it manually, the same way you added the MDF file

To backup and restore the backup into a new database, see these articles:

Create a Full Database Backup (SQL Server)

Restore a Database Backup (SQL Server Management Studio)

Milena Petrovic
  • 2,651
  • 21
  • 18
  • Thanks for your reply Milena. I am having the exact same issue. I can see the file in the explorer but cannot see it when I try to attach a database. Also I see the other databases that are present there in SQL server, they point to the same location where I moved the database but I cannot see them in the file explorer. This is really confusing me. My same account works fine on a different server and I dont have any issues attaching a database. – user2149838 Mar 21 '14 at 16:21
  • Can you use Windows authentication on SQL Server? – Milena Petrovic Mar 21 '14 at 16:27
  • Hey Milena, I figured it out. I feel so stupid at this point, I was connecting to a different server. Its all good, I was able to attach the database. Thanks again for all your help :) – user2149838 Mar 21 '14 at 16:51