Skip to main content
Skip table of contents

Attaching and detaching SQL databases

This support note applies to:

  • AO Document Manager (AU)
  • AO Document Manager (NZ)
  • AO Tax (NZ)
  • AE Statutory Reporter (NZ)
  • AO Practice Manager (NZ)
  • AO Practice Manager (AU)
  • AO Workpapers (AU)
  • AO Statutory Reporter (AU)
  • AO Tax (AU)
  • AE Workpapers (AU)
  • AE Statutory Reporter (AU)
  • AE Document Manager (NZ)
  • AE Practice Manager (NZ)
  • AE Practice Manager (AU)
  • AE System Release (NZ)
  • AE System Release (AU)
  • AE Document Manager (AU)
  • Insolvency Old (AU)
  • Insolvency (AU)
  • AE Tax (NZ)
  • AO Corporate Compliance (AU)
  • AE Tax Series 6 & 8 (AU)
  • AE Tax (AU)
  • AE Corporate Compliance (AU)
Article ID: 34709

When using MYOB applications which have MSSQL databases, in certain instances, you may be required to perform a reinstall of SQL, such as when starting the SQL service generates the 3417 error. In these instances, any data that was entered after the latest backup was taken would be lost if you restore from a backup. However, the live SQL databases will still exist in the installation directory of SQL. Therefore, you are able to attach the databases into the instance without losing data.

This also applies if the database is marked as suspect or stuck in single user mode , for example you cannot expand the database in SQL Server Management Studio, which means MYOB cannot access it. In these cases, you should be able to detach and then reattach them to SQL without any data loss.

There are two ways to attach or detach a database - through SQL Server Management Studio or through a Transact-SQL script, which can be run as a query in SQL Managment Studio, from a pre-defined script, or through an sqlcmd or osql command.

This procedure is not covered by MYOB support and database maintenance and backups are your own responsibility. If you encounter issues or have trouble following the above processes, please contact your IT support for further assistance.
To attach and detach an SQL database through SQL Server Management Studio

To attach the database

  1. Follow the menu path: Start > Programs > Microsoft SQL Server 20xx > SQL Server Management Studio Express. The Connect to Server login screen appears.

    20xx refers to the currently installed / configured version of SQL for MYOB.

  2. Ensure the Server Name, Login and Password are correct and click Connect. The Microsoft SQL Server Management Studio Express screen appears.

  3. Right-click on the Databases folder and select Attach. The Attach Databases screen appears.

  4. Click Add and locate the mdf file of the database you wish to attach, then click OK twice. The database is created and the mdf file is imported. 

For further information refer to the Microsoft Developer's Network (MSDN).

To detach the database

  1. Follow the menu path: Start > Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio Express. The Connect to Server login screen appears.
  2. Ensure the Server Name, Login and Password are correct and click Connect. The Microsoft SQL Server Management Studio Express screen appears.
  3. Click the [+] symbol to expand Databases. All databases in the instance are shown.
  4. Right-click on the database to detach and choose Tasks > Detach. The Detach Database window appears.

  5. Click OK to detach the database without deleting the mdf or ldf files. The database is detached from SQL. 

 For further information refer to the Microsoft Developer's Network (MSDN). 

  

To attach and detach a SQL database through Transact-SQL query

To attach the database

  1. Follow the menu path: Start > Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio Express. The Connect to Server login screen appears.
  2. Ensure the Server Name, Login and Password are correct and click Connect. The Microsoft SQL Server Management Studio Express screen appears.
  3. Press the New Query button in the top left. A blank query window appears in the middle of the screen.
  4. Type the following query:
    CREATE DATABASE dbname
    ON (FILENAME = 'mdfpath'),
    =(FILENAME = 'ldfpath')
    FOR ATTACH; Where dbname is the name of the database you are attaching, mdfpath is the location of themdf file i.e C:\Program Files\Microsoft SQL Server\Data\DB.mdf and ldfpath is the location of the ldf file i.e C:\Program Files\Microsoft SQL Server\Data\DBlog.ldf.

  5. Click Execute in the top left corner. The command is executed and the database is attached.

    For further information refer to the Microsoft Developer's Network (MSDN).

To detach the database

  1. Follow the menu path: Start > Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio Express. The Connect to Server login screen appears.

  2. Ensure the Server Name, Login and Password are correct and click Connect. The Microsoft SQL Server Management Studio Express screen appears.

  3. Press the New Query button in the top left. A blank query window appears in the middle of the screen.
  4. Type the following query:
    EXEC sp_detach_db 'dbname', 'true';
    Where dbname is the name of the database you are detaching.

  5. Click Execute in the top left corner. The command is executed and the database is detached.

    For further information refer to the Microsoft Developer's Network (MSDN).  
To attach and detach a SQL database through Transact-SQL osql or sqlcmd command

To attach the database

  1. Click Start, type cmd in the search field and press ENTER. The Command Prompt screen appears.

  2. Type one of the following commands and press ENTER:
    OSQL: osql -S servername\instancename -E -Q "CREATE DATABASE dbname ON (FILENAME = 'mdfpath'), (FILENAME = 'ldfpath') FOR ATTACH"
    SQLCMD: sqlcmd -S servername\instancename -E -Q "CREATE DATABASE dbname ON (FILENAME = 'mdfpath'), (FILENAME = 'ldfpath') FOR ATTACH"
    Where servername is the name of the SQL Server, instancename is the name of the instance not needed if installed to the default instance, dbname is the name of the database you are attaching, mdfpath is the location of the mdf file i.e C:\Program Files\Microsoft SQL Server\Data\DB.mdf and ldfpath is the location of the ldf file i.e C:\Program Files\Microsoft SQL Server\Data\DBlog.ldf. The command is executed and the selected database is attached to SQL. 

    For further information refer to the Microsoft Developer's Network (MSDN).

To detach the database

  1. Click Start, type cmd in the Search field and press ENTER. The Command Prompt screen appears.

  2. Type one of the following commands and press ENTER: OSQL: osql -S servername\instancename -E -Q "EXEC sp_detach_db 'dbname', 'true'" SQLCMD: sqlcmd -S servername\instancename -E -Q "EXEC sp_detach_db 'dbname', 'true'" Where servername is the name of the SQL Server, instancename is the name of the instance (not needed if installed to the default instance) and dbname is the name of the database you are detaching. The command is executed and the selected database is detached from SQL. 

     

    For further information refer to the Microsoft Developer's Network (MSDN).  

 The names of your databases are normally VPMSER, AEDBn or AODBn for the Practice Manager/Client Framework database, and SQLTAX, AETAXn or AOTAXn for the Tax database; where n is a number for example AEDB1 or AODB2 etc.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close