SqlServer的一些有用sql技巧

1.数据库备份

--语句
backup database "database_name" TO DISK='path';
--示例
backup database TEST TO DISK='D:\历史备份文件\20181010\TESTBACKUP.bak'; 

2.查看主机操作数据库

exec sp_who

3.查询索引碎片

use DATABASE_NAME;
SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and dbindexes.[name] not like 'pk_%'
ORDER BY indexstats.avg_fragmentation_in_percent * page_count desc

4.查询数据库大小

SELECT 
    DB_NAME(database_id) AS [Database Name],
    [Name] AS [LogicalName],
    [Physical_Name] AS[Physical Name],
    ((size *8) / 1024) AS [Size(MB)],
    [differential_base_time] AS[Differential Base Time]
FROM 
    sys.master_files 
WHERE DB_NAME(database_id) IN('Conductor')

5.查看数据库增长速率

dbcc sqlperf(logspace);