SQL Database File Information

Where is the database data file (.mdf) saved in SQL Server?

SELECT

   type_desc,

   name,  -- logical name for data file

   physical_name, -- physical data file

   size AS size_mb

FROM sys.master_files  WITH (NOLOCK)

WHERE database_id = DB_ID('your_database_name')

          AND file_id = 1 AND type_desc = 'ROWS'

 

 

Where is the database log file (.ldf) saved in SQL Server?

SELECT

   type_desc,

   name,  -- logical name for log file

   physical_name, -- physical log file

   size AS size_mb

FROM sys.master_files  WITH (NOLOCK)

WHERE database_id = DB_ID('your_database_name')

          AND file_id = 2 AND type_desc = 'LOG'

 

 

Where is the database backup file (.bak) saved in SQL Server?

SELECT DISTINCT

   database_name,

   physical_device_name,

   backup_start_date,

   backup_finish_date,

   backup_size/(1024*1024) AS backup_size_mb,

   CASE b.type

      WHEN 'D' THEN 'FULL'

      WHEN 'L' THEN 'LOG'

   END AS 'Backup Type'

FROM msdb.dbo.backupset b

   JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id

WHERE database_name = 'your_database_name'

ORDER BY backup_finish_date DESC

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF