As a DBA or even NAV Admin this topic is very important. In my other post (Physical Database Structure of a Dynamics NAV SQL database) I explained database files and their file extensions.
Let’s say you need to move the logfile because of certain reasons like diskspace. The first thing to do is to Detach the database. This can be accomplished via SQL Server Management Studio or by using the Stored Procedure: sp_detach_db:
USE [master]
ALTER DATABASE [Demo Database NAV (10-0) RTM] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_detach_db 'Demo Database NAV (10-0) RTM'
Note: detaching a database is not like droppping a database. Dropping a database is deleting the database and their physical files: everything is gone and lost. Detaching a database only removes the ‘reference’ to your database. All your files and data are still available. After detaching your database you could move your files to another directory or disk. After moving attach your database again to bring the database online again.
Very useful queries thats show all databases and all database files:
select * from sys.databases
select * from sys.master_files
By the way: technically speaking the sys.databases is actually a view that resides in the msdb system database. Want to know more about SQL Views? Please read my post about SQL Database Objects.
So let’s create a user story: As a NAV admin you would like to move your Logfile (LDF) to another disk/directory.
High level steps:
– Inform your organization of the maintenance window (NAV will be unavailable)
– Stop all NST’s (NAV Service Tiers) that are connected to the database.
– Detach the database
– Move your logfile to another directory
– Attach the database
– Start all NST’s (NAV Service Tiers) that are connected to the database
– Dont’t forget to check if everything is working again
To attach the database please specify the new path of your LDF file:
CREATE DATABASE [Demo Database NAV (10-0) RTM] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Demo Database NAV (10-0) RTM.mdf' ),
( FILENAME = N'D:\MSSQL\Logfile\Demo Database NAV (10-0) RTM_log.ldf' )
FOR ATTACH
GO
Now what if the path to the logfile is incorrect? You will then get an error like this:
Msg 5120, Level 16, State 101, Line 5
Unable to open the physical file “D:\MSSQL\Logfile\Demo Database NAV (10-0) RTM_log.ldf”. Operating system error 2: “2(The system cannot find the file specified.)”.
Msg 1802, Level 16, State 7, Line 5
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Obvious what to do next… please provide the correct path to the LDF. Is your LDF corrupt or want to start with a new one? Thats possible too! Like this:
CREATE DATABASE [Demo Database NAV (10-0) RTM] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Demo Database NAV (10-0).mdf' )
FOR ATTACH_REBUILD_LOG
GO
Note: this will rebuild the log in the same directory as where the MDF is created. Mission not accomplished because the logfile is still not moved to another disk. However your database is attached again and online but without the ‘original’ LDF you intended to move :)
Microsoft Azure, Microsoft Dynamics and more... 