SQL Server DBA Cheat Sheet (DRAFT) by johnwang2616

/* Full Backup */
BACKUP DATABASE [DATABASE_NAME] 
	TO DISK = 'C:\...' /* Absolute Path */
	WITH
		COMPRESSION  /* Save host capacity */

/* Differential Backup */
BACKUP DATABASE [DATABASE_NAME] 
	TO DISK = 'C:\...' /* Absolute Path */
	WITH
		COMPRESSION  /* Save host capacity */
	,	DIFFERENTIAL




RESTORE DATABASE [DATABASE_NAME] 
	FROM DISK = 'C:\...' /* Absolute Path */
	WITH
			REPLACE /* Give Up Old Data. Replace Database */
		,	STATS = 10 /* Every 10% Return On Progress */
		,	RECOVERY

SELECT
		sys.sysprocesses.spid
	,	sys.sysprocesses.dbid
	,	DB_NAME(sys.sysprocesses.dbid) AS DBNAME
	,	sys.sysprocesses.cpu
	,	sys.sysprocesses.physical_io
	,	sys.sysprocesses.memusage
	,	sys.sysprocesses.login_time AS PROC_LOGIN
	,	sys.sysprocesses.last_batch AS PROC_LAST_RESPOND
	,	DATEDIFF
			(
				MINUTE
			,	sys.sysprocesses.login_time
			,	sys.sysprocesses.last_batch
			)
		AS SPENT_MINUTE
	,	sys.sysprocesses.status AS PROC_STATUS
	,	sys.sysprocesses.hostname AS HOST
	,	sys.sysprocesses.program_name AS PROGRAM
	,	sys.sysprocesses.loginame AS SQL_LOGIN
	,	sys.dm_exec_sql_text.text AS SQL_TEXT
FROM sys.sysprocesses CROSS APPLY sys.dm_exec_sql_text(sys.sysprocesses.sql_handle)

USE [DatabaseName] GO

ALTER DATABASE [DatabaseName] 
	SET RECOVERY SIMPLE WITH NO_WAIT

DECLARE @TARGET_MB AS INT=1
DBCC SHRINKFILE(Database_LOG_logic_Name,@TARGET_MB)

ALTER DATABASE [DatabaseName]
	SET RECOVERY FULL WITH NO_WAIT

GO