grzech.webio.pl

RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE,RECOVERY Źródło: http://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state

Generowanie certyfikatu: makecert -r -pe -n "CN=NAZWA" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 -a sha1 Domyślna ścieżka dla kluczy prywatnych: C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys Listowanie aktywnych połączeń do serwera: SELECT * FROM sys.dm_exec_connections Szukanie pliku klucza prywatnego dla certyfikatu o podanym "odcisku kciuka": FindPrivateKey My LocalMachine -t "‎NUMER" -a Źródło: http://thesqldude.com/2011/10/22/to-ssl-or-not-to-ssl-that-is-the-question/...

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER FOR DROP_DATABASE AS --log attempt to drop database DECLARE @db VARCHAR(209) SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) + ' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)')) RAISERROR(@db, 16, 1)WITH LOG --prevent drop database ROLLBACK GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO --turn on trigger ENABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER Zródło: http:...

sp_cycle_errorlog

SELECT SPID = s.session_id, s.[host_name], s.[program_name], s.status, s.memory_usage, granted_memory = CONVERT(INT, r.granted_query_memory*8.00), t.text, sourcedb = DB_NAME(r.database_id), workdb = DB_NAME(dt.database_id), mg.*, su.* FROM sys.dm_exec_sessions s INNER JOIN sys.dm_db_session_space_usage su ON s.session_id = su.session_id AND su.database_id = DB_ID('tempdb') INNER JOIN sys.dm_exec_connections c ON s.session_id = c.most_recent_session_id LEFT OUTER JOIN sys.dm_exec_requests r ON r.session_id = s.session_id LEFT OUTER JOIN ( SELECT session_id, database_id FROM sys.dm_tran_session_transactions...

ALTER DATABASE tempdb ADD FILE ( name = tempdb_secondary, filename = 'X:\XXX\tempdb_secondary.ndf', size = 512 MB )

SELECT 'ALTER SCHEMA [NAZWABAZY] TRANSFER [' + s.Name + '].[' + o.Name + ']' FROM sys.Objects o INNER JOIN sys.Schemas s on o.schema_id = s.schema_id WHERE s.Name = 'grafnet' And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')

SELECT t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, p.Rows ORDER BY t.Name Źródło: http://stackoverflow.com/questions/7892334/get-size-of...

USE master; GO CREATE PROCEDURE sp_FixSeeds2012 AS BEGIN --foreach database DECLARE @DatabaseName varchar(255) DECLARE DatabasesCursor CURSOR READ_ONLY FOR SELECT name FROM sys.databases where name not in ('master','tempdb','model','msdb') and sys.databases.state_desc = 'online' OPEN DatabasesCursor FETCH NEXT FROM DatabasesCursor INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('USE ['+@DatabaseName + '] --foreach identity column DECLARE @tableName varchar(255) DECLARE @columnName varchar(255) DECLARE @schemaName varchar(255) DECLARE IdentityColumnCursor CURSOR READ_ONLY FOR select TABLE_NAME...

SET NOCOUNT ON GO DECLARE @DBName VARCHAR(255) DECLARE @SQL VARCHAR(MAX) DECLARE @SQLEXEC VARCHAR(MAX) CREATE TABLE #TableVar (DatabaseName varchar(256), permission_name varchar(32), State_desc varchar(10)) DECLARE curDatabases CURSOR STATIC FOR SELECT [name] FROM master.sys.databases WHERE database_id > 4 ORDER BY [name] OPEN curDatabases FETCH NEXT FROM curDatabases INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'insert into #TableVar SELECT DB_NAME() as DatabaseName,permission_name,state_desc FROM sys.database_permissions where grantee_principal_id = user_id(''''guest'''') AND state...