How to: Alter Databases

You can change the database property settings that you selected when you created the database.

The Alter Database window gives you access to the same settings as the New Database window.

Note

You cannot alter a database by using the development environment if the database is deployed on Azure SQL Database.

To alter a database

1. In the development environment, on the File menu, choose Database, and then choose Alter. The Alter Database window opens. In the Alter Database window you can change database settings.

   The Alter Database window contains the following tabs:

   • Altering Databases - General Tab

   • Altering Databases - Database Files Tab

   • Altering Databases - Transaction Log Files Tab

   • Altering Databases - Collation Tab

   • Altering Databases - Options Tab

   • Altering Databases - Integration Tab

   • Altering Databases - Advanced Tab

2. Restart the Microsoft Dynamics NAV Server instance.

   The changes will not take effect until you restart the Microsoft Dynamics NAV Server instance. For more information, see How to: Start, Stop, Restart, or Remove a Microsoft Dynamics NAV Server Instance.

Changing Database Collation

You cannot change the collation directly in the current database. To change the collation, you must create a new database that uses the correct collation, and then export the data from the old database and import it to the new database. You can do this by using SQL Server Management Studio and the Microsoft Dynamics NAV Administration Shell as outlined in the following procedure.

To change the collation of a Microsoft Dynamics NAV database

1. In SQL Server Management Studio, create a new database that uses the desired collation.

   Make sure that the service account of the Microsoft Dynamics NAV Server instance that will connect to the database has proper permission to the database. The service account must be a member of the db_owner role of the database. For more information see Provisioning the Microsoft Dynamics NAV Server Account.

2. To export the data from the old database to the new database, open the Microsoft Dynamics NAV Administration Shell, and run the Export-NAVData cmdlet as shown in the following example.

   Export-NAVData -DatabaseServer DatabaseServerName -DatabaseName OldDatabaseName -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -FilePath c:\Files\MyNavDB.navdata
   

   For more information, see Export-NAVData cmdlet.

3. To import the data from the old database to the new database, run the Import-NAVData cmdlet as shown in the following example.

   Import-NAVData -DatabaseServer DatabaseServerName -DatabaseName NewDatabaseName -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -FilePath c:\Files\MyNavDB.navdata
   

   For more information, see Import-NAVData cmdlet

4. Connect the new database to the Microsoft Dynamics NAV Server instance.

   For more information, see How to: Connect a Microsoft Dynamics NAV Server Instance to a Database.

5. Synchronize the table schemas in the database. You can do this from the development environment or Microsoft Dynamics NAV 2016 Administration Shell.

   • From the development environment, on the Tools menu, choose Sync. Schema For All Tables, and then choose With Validation and follow the schema synchronization instructions.

   • From the Microsoft Dynamics NAV 2016 Administration Shell, open the Microsoft Dynamics NAV 2016 Administration Shell as an administrator, and then run the Sync-NavTenant cmdlet. For more information, see How to: Run the Sync-NAVTenant Cmdlet to Synchronize the Tenant Database with the Application Database.

6. Restart the Microsoft Dynamics NAV Server instance.

   For more information, see How to: Start, Stop, Restart, or Remove a Microsoft Dynamics NAV Server Instance.

See Also

Reference

Alter Database