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.