Useful commands for MSSQL DBA

In this post we will see some useful handy commands for SQL Server DBA.

1) Backups Commands

For Full Backup

BACKUP DATABASE [SampleDB2020] TO DISK = N'C:\Jhachandan\Backup Files\SampleDB2020_Full.bak' WITH NOFORMAT, NOINIT, NAME = N'SampleDB2020-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

For Differential Backup

BACKUP DATABASE [SampleDB2020] TO DISK = N'C:\Jhachandan\Backup Files\SampleDB2020_Diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'SampleDB2020-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

For Transactional Log Backup

BACKUP LOG [SampleDB2020] TO DISK = N'C:\Jhachandan\Backup Files\ SampleDB2020_Log.trn' WITH NOFORMAT, NOINIT, NAME = N'SampleDB2020-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

2) Restore Commands

For Restore Database

RESTORE DATABASE [SampleDB2020] FROM DISK = N'C:\Jhachandan\Backup Files\SampleDB2020Full.bak' WITH FILE = 1, MOVE N'SampleDB2020_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TEST\MSSQL\DATA\SampleDB2020.mdf', MOVE N'SampleDB2020_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TEST\MSSQL\DATA\SampleDB2020_1.LDF', NOUNLOAD, STATS = 10

Restoration Database with Recovery

RESTORE DATABASE [SampleDB2020] WITH RECOVERY (for bring online database)
SP_HELP_REVLOGIN

Restore Database up to Specific Time only

RESTORE DATABASE [SampleDB2020] FROM DISK = [N'C:\Jhachandan\Backup Files\SampleDB2020Full.bak'] WITH NORECOVERY
GO
RESTORE LOG [SampleDB2020] FROM DISK = N'C:\Jhachandan\Backup Files\ SampleDB2020_Log.trn ' WITH RECOVERY,
STOPAT= ‘MARCH 23, 2009 05:31:00 PM’
GO

3) Checking to make sure a SQL Server backup is useable

 RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK
 GO

4) For used space on the particular database

SP_SPACEUSED

5) Check the update statistics if SQL Server

SP_UPDATESTATS

6) Display the Configuration Setting of the Current Server

SP_CONFIGURE

7) Check the Log Space of All Databases of Server

DBCC SQLPERF(LOGSPACE)

8) Check for the Drive Size of the Physical Server

XP_FIXEDDRIVES

9) For Check the Error Log

SP_READERRORLOG

10) Data File and Log File Location for All Server

SELECT DB_NAME (DATABASE_ID), NAME, TYPE_DESC, PHYSICAL_NAME, [SIZE(MB)] = (SIZE * 8) / 1024.0 FROM SYS.MASTER_FILES

11) For Missing Index Details

SELECT *FROM SYS.DM_DB_MISSING_INDEX_DETAILS (MISSING INDEX)

12) Check Recovery Model

SELECT NAME, RECOVERY_MODEL_DESC FROM SYS.DATABASES
WHERE NAME = ['DB_NAME']

13) Getting All Table Name from particular database

USE [DB_NAME]
GO
SELECT * FROM SYS.TABLES
GO
Number of Cores on the Server
SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS NumberOfCoresInEachCPU, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) ) END AS TotalNumberOfCores, cpu_count AS NumberOfLogicalCPUs
, CONVERT(MONEY, Round(physical_memory_in_bytes / 1073741824.0, 0)) AS TotalRAMInGB
FROM
sys.dm_os_sys_info

14) Check Dead Lock on Server

SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

15) Checking Locking Process

EXEC SP_LOCK
EXEC SP_WHO2
USE [DB_NAME]
GO
SELECT * FROM SYS.DM_TRAN_LOCKS
GO

16) Bring Database Offline

ALTER DATABASE [DB_Name] SET OFFLINE WITH
ROLLBACK IMMEDIATE

17) Bring Database Online

ALTER DATABASE [DB_Name] SET ONLINE

18) Check which Backup Failed on when

EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current
EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)
EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)

19) Most Recent Database Backup for Each Database

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON sdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' GROUP BY msdb.dbo.backupset.database_name ORDER BY 
 msdb.dbo.backupset.database_name

20) Database Backups for all databases For Previous Week

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name,  msdb.dbo.backupset.backup_start_date,  msdb.dbo.backupset.backup_finish_date,  msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type,  msdb.dbo.backupset.backup_size,  msdb.dbo.backupmediafamily.logical_device_name,  msdb.dbo.backupmediafamily.physical_device_name,  msdb.dbo.backupset.name AS backupset_name,  msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date

21) Check Database ISOLATION Level

DBCC USEROPTIONS

22) Buffer Cache Hit Ratio

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'

23) Who is disable the job on sql server

EXEC msdb.dbo.sp_helpjob

24) Physical Location for the database files (data file and log file)

SELECT NAME, PHYSICAL_NAME AS CURRENT_FILE_LOCATION FROM SYS.MASTER_FILES

25) Checking the Log shipping Last Backup LSN Number for backups of primary and secondary server.

SELECT SECONDARY_DATABASE, LAST_COPIED_FILE, LAST_RESTORED_FILE FROM MSDB..LOG_SHIPPING_MONITOR_SECONDARY

26) Find SQL Server Startup Time.

SELECT SQLSERVER_START_TIME FROM SYS.DM_OS_SYS_INFO

27) Find SQL Server Database Size.

USE DATABASE [DATABASE_NAME]
GO;
SELECT DBNAME, NAME, [FILENAME], SIZE AS 'SIZE(MB)', USEDSPACE AS 'USEDSPACE(MB)', (SIZE - USEDSPACE) AS 'AVAILABLEFREESPACE(MB)'
FROM 
( SELECT DB_NAME(S.DATABASE_ID) AS DBNAME, S.NAME AS [NAME], S.PHYSICAL_NAME AS [FILENAME], (S.SIZE * CONVERT(FLOAT,8))/1024 AS [SIZE], (CAST(CASE S.TYPE WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(S.NAME, 'SPACEUSED') AS FLOAT)* CONVERT(FLOAT,8) END AS FLOAT))/1024 AS [USEDSPACE], S.FILE_ID AS [ID]
FROM
SYS.FILEGROUPS AS G INNER JOIN SYS.MASTER_FILES AS S ON ((S.TYPE = 2 OR S.TYPE = 0) AND S.DATABASE_ID = DB_ID() AND (S.DROP_LSN IS NULL)) AND (S.DATA_SPACE_ID=G.DATA_SPACE_ID)
) DBFILESIZEINFO

That's all in this article. Please Like, Share and Subscribe to learn more.