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
|
GetExecutingSqlTextOnDatabase
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)，包括正在執行的語法
ClearDatabaseLog (ResetLogFileToNewValue)
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
|
Database_LOG_logic_Name=資料庫記錄檔(.ldf)的邏輯名稱
查詢如下
SELECT
[LOGIC_NAME]=name
, physical_name
FROM sys.database_files
|
