Move LDF, MDF or NDF file in SQL Server 2016 using Alter Database

In my previous post I explained how to move a certain database file with ATTACH/DETACH. There is another better option which I would like to share with you.:

Let’s say you need to move the NDF file. The first thing to do is to alter the database. Specify the new path of the NDF file like this:

ALTER DATABASE [NAVTest]
MODIFY FILE
(
NAME = Data
FILENAME = 'C:\NewPath\Data.ndf'
)

In order to move the NDF file you first need to take the database Offline:

use master
ALTER DATABASE [NAVTest] set offline

Now move your file and set the database online again:

use master
ALTER DATABASE [NAVTest] set Online

So in summary we have the following high level steps:

– Alter database and specify the new path of one or multiple files
– Set database Offline
– Move the physical file
– Set database Online

Now what if the path to one or more files is incorrect? You will then get a warning and the database will be in Suspect Mode.

 

 

Move LDF, MDF or NDF file in SQL Server 2016 using Attach/Detach

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 :)

SQL Database Objects

In this post I will tell something about Basic Database Objects in SQL like Databases, Tables, Views, Stored Procedures and Triggers.

What is a Database?
A database contains a set of tables and other objects. Each database ofcourse has a name.

What is a table?
Within a database you create tables that store information about something. For example a simple Table that stores Persons and information about them like names and their ages. For every field we need to specify what data it is going to hold (datatype). Will it, for example contain a boolean, text or integer data?

What is a view?
With a view you could obscure the real tablename for a virtual one. A big advantage is that you can define for example a WHERE clause in your view. This way when referring to this view you don’t need to specify the where clause. This makes certain operations like building or maintaining the query for your users a lot simpler!

What is a Stored Procedure and Trigger?
A stored procedure is prepared SQL code that you save. This way you can reuse it over and over again just like functions in programming languages. Triggers do the same thing except that they will be only executed when they are triggered by an event. So triggers can’t be executed manual, Stored Procedures do.

What is a Synonym?
Synonyms are a great function to obscure table names. What if a table name changes every day because of archiving reasons? With a synonym you could easily fix this problem. Synonyms are being used to redirect to a source that changes regularly like Archive Tables. This way you don’t need to hardcode the reference to the specific database.

Object Metadata Table (C/AL translated into C#)

Before you can run or execute a Microsoft Dynamics NAV object (Codeunit for example), you must compile the object. By Compiling the object your C/AL code will be transformed into C# Code. NAV only runs compiled C/AL code which is transformed into C# Code. This code is stored in table ‘2000000071 – Object Metadata’

To compile an object, do the following:

• Start the Development Environment.
• Open your NAV database. Select an object.
• In the Object Designer, Go to Tools and then choose Compile.

After your object has been successfully compiled the field [User Code] in the Object Metadata contains the C# Code.

Note: if you create an object without any C/AL code then the [User AL Code] field will be empty. The [User Code] field will also be empty, even after Compiling.