Show Menu
Cheatography

SQL Server DBA Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

BACKUP & RESTORE DATABASE 2008 VERSION

/* 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

GetExe­cut­ing­Sql­Tex­tOn­Dat­abase

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)
取得正在執行­的執行­緒與其­資料(­process & info),­包括正­在執行的語法

ClearD­ata­baseLog (Reset­Log­Fil­eTo­New­Value)

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
Databa­se_­LOG­_lo­gic­_Na­me=­資料庫­記錄檔­(.l­df)­的邏輯名稱
查詢如下
SELECT
[LOGIC­_NA­ME]­=name
, physic­al_name
FROM sys.da­tab­ase­_files