Moving database files

We have three kinds of database files: log files (*.ldf), primary data files (*.mdf) and secondary data files (*.ndf).

Primary data file is the starting point for a database and points to all other files in it. Each database has one primary data file. There can be 0 or >0 secondary data files in a database to make up data files other than primary data file. Log files hold all log information that is used to recover database. There must be at least one log file for a database but it can be more than one.

If you initially create a database by using default locations or defining new one at run time. After sometime if you want to move database files to a new location by considering capacity of current disk or for any other system maintenance.

How you will move them?

Let’s see below!

We have two considerations to move database files from one location to another.
a) Planned relocation procedure
b) Relocation for scheduled disk maintenance

Although you can follow both procedures alternatively, but it depends on the environment and scenario in which you are working. If your concern with only db files move then “Planned relocation procedure” is most suitable in every scenario, because it affects the processes only for relevant database, till the time it comes online.

Planned relocation procedure:
You can follow this procedure when you require less downtime for production, or to minimize relocation impact on other process like replication, log shipping etc, mostly when you want to move files permanently to another location without affecting other databases.

Steps are:
1) Run ALTER DATABASE [database name] SET OFFLINE.
2) Move file to new location
3) Run ALTER DATABASE [database name] MODIFY FILE ( NAME = [logical name], FILENAME = 'path and filename'.
4) Run ALTER DATABASE [database name] SET ONLINE.


Relocation for scheduled disk maintenance:
You can follow this procedure, when you need to move database files to another location to perform any server or disk maintenance activity, like disk de-fragment and you can bear downtime for all databases available on that particular server.


Steps are:
1) Run ALTER DATABASE [database name] MODIFY FILE ( NAME = [logical name], FILENAME = 'path and filename'.
2) Sql server is stopped or system is shutdown to perform maintenance.
3) Move file to new location.
4) Restart server.

Preliminary checks for MS DTC

To dig further to evaluate MS DTC error, check below things is in place.

1. You are able to ping both server from each other. If not then define them in their host files and try to ping them again, till it resolves.
2. All MS DTC configurations should be same on both servers.
   a) NETWORK DTC Access
   b) Client and Administration (Allow remote clients, Allow remote administration)
   c) Transaction Communication Manager (Allow Inbound, Allow outbound, Mutual authentication required, Incoming caller authentication required, No authentication required, Enable TIP Transactions)
   d) Enable XA Transaction
   e) DTC Logon Account [Should be NT Authoriy/Networkservice]
   
   Permissions: You must have administrator access to play with above configurations.
   
   How to access MS DTC?
   Control Panel > Administrative Tools > Component Services > Component Services > Computers > My Computer > right click on it and then click on Properties and then click on MS DTC tab.
   
   How to access Host file?
   "C:\Windows\System32\Drive\etc\", you will find a file with the name of "Host"