Here is a start of a regular posting, “useful commands” Over the years I have saved snippets, commands and notes of things that have been useful or saved alot of time. I start off with a gathering of T-SQL commands to do useful things within a windows database. Some of these relate directly to modifying a Kentico database (the CMS of choice where I work), but may be useful anyway
-- Turn off SSL
UPDATE [CMS_SettingsKey] SET KeyValue = 'False'WHERE KeyName LIKE 'CMSUseSSL%'
-- Take Database offline forcefully and come back online
#Go offline
USE master
GO
ALTER DATABASE YourDatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
#Go online
USE master
GO
ALTER DATABASE YourDatabaseName
SET ONLINE
GO
# get out of recovery mode
RESTORE DATABASE <database name> WITH RECOVERY
-- Get MDF/BAK file SQL version
#Bak file (in sql)
RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\base106.bak'
#MDF (powershell)
get-content -Encoding Byte "...\foo.mdf" | select-object -skip 0x12064 -first 2 number 2 * 256 + number 1
-- Get DB Size
EXEC sp_spaceused
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT *
FROM #t
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #t
DROP TABLE #t
-- Get RAM usage of server
SELECT db = DB_NAME(t.dbid), plan_cache_kb = SUM(size_in_bytes/1024)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.dbid < 32767
GROUP BY t.dbid
ORDER BY plan_cache_kb DESC;