SQL Server database files

SQL Server databases have three types of files:

• Primary data files - have the .mdf extension by default. Every database has one primary data file.
• Secondary data files - have the .ndf extension by default. Secondary data files are optional. Some databases may not have them at all, while other databases may have several secondary data files.
• Log files - have the .ldf extension by default. Every database has a least one log file.

Make sure that all of the volumes containing the above files are included in the backup. For example, if your databases are located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ and log files are located in F:\TLs\, you need to back up both volumes C:\ and F:\.

Determining paths to all database files of an instance by using Transact-SQL

The following Transact-SQL script can be used "as is" to determine paths to all database files of an instance.

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace
    From sys.database_files'
Select * From ##temp
drop table ##temp

Determining locations of database files by using SQL Server Management Studio

Default locations

SQL Server database files are in their default locations unless you have customized the paths manually. To find out the default locations of database files:

1. Run Microsoft SQL Server Management Studio and connect to the necessary instance.
2. Right-click the instance name and select Properties.
3. Open the Database Settings page and view the paths specified in the Database default locations section.

Custom locations

If SQL Server database file locations were customized, proceed as follows.

1. In Microsoft SQL Server Management Studio, expand the necessary instance.
2. Right-click the database, and then click Properties. This will open the Database Properties dialog box.
3. In the Select a page pane, click Files and view the paths specified in the Database files section.