Move SQL server database files

-- 1. set DB offline:

ALTER DATABASE MyDataBase SET OFFLINE;

-- 2. move the files

ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile', FILENAME = 'E:\SQL\Data\MyBbFile.mdf' );

ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile_log', FILENAME = 'E:\SQL\Log\MyBbFile_log.ldf' );

ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile_BLOBStorage', FILENAME = 'E:\SQL\BLOB\MyBbFile.ndf' );

-- 3. copy-paste the files to the new folder (via Windows)

-- 4. set DB online:

ALTER DATABASE MyDataBase SET ONLINE;

-- get data of the DB files:

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'MyDataBase');

Get tables size

SELECT t.NAME AS TableName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB, 

SUM(a.used_pages) * 8 AS UsedSpaceKB, 

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID 

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

WHERE t.is_ms_shipped = 0

GROUP BY t.Name, p.Rows

ORDER BY TotalSpaceKB desc

Get aggregations of few columns for each record - using 'Values' operator

SELECT ValuesTestID, ValuesTestName, 

IntCol1, IntCol2, IntCol3, IntCol4, IntCol5, IntCol6,

( Select MAX(ValuesTestName)

From    (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6)) 

UniqueColumn(ValuesTestName)

) MaxIntCol,

( Select SUM(ValuesTestName)

From    (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6)) 

UniqueColumn(ValuesTestName)

) SumIntCol

FROM ValuesTest

Get DBs files locations and drive details

SELECT DISTINCT 

dovs.database_id as database_id,

DB_NAME(dovs.database_id) as DBName,

mf.physical_name as PhysicalFileLocation,

dovs.logical_volume_name as LogicalDriveName,

dovs.volume_mount_point as Drive,

CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

ORDER BY DB_NAME(dovs.database_id)

Get Disk Space on Server

EXEC MASTER..xp_fixeddrives