Basic SQL - How to attach a database to SQL Server

  • Article

This post is part of "Overview of NAV-specific SQL features for application consultants".

If you receive a SQL Server database file (.MDF), then you must attach this to your SQL Server before you can access it. This is how to do that:

1)     Copy the Database file (.MDF), for example “Demo Database NAV (6-0)_Data.MDF” into the folder where SQL Server keeps the database files. On SQL Server 2008 this defaults to “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”:

CopyDBFile

2)     Open SQL Server Management Studio. Note: On Windows Vista / Windows 2008, you must open it as Administrator to have the right permissions. Otherwise the next steps might fail, and maybe without giving you any error messages:

OpenAsAdmin

3)     In SQL Server Management Studio right click on Databases, select Attach... click Add, then select the file you copied in step 1, then click OK:

AddDB

4)     The database file may be pointing to a transaction log which you don’t have and don’t need. So, before clicking OK again, check if there is a reference to the transaction log under “Database Details”. Remove it if there is, and then click OK:

RemoveTransactionLog

5)     If you receive any warnings about adding catalogues, then ignore those. As long as you now see the new database in SQL Server Management Studio:

DatabaseAttached

You should now be able to open the database with a NAV client. Note, the SQL backup will contain any user IDs and permissions that were created in the database, so you would need to know what login to use. In the case of Windows logins, of course if the database came from another system / another domain, then any Windows logins will not be valid on your domain.

 

 

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.