Point in Time Recovery

-- Check Recovery Model

SELECT name,recovery_model_desc
FROM sys.databases


-- Change Recovery Model

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT

/*After problem has occurred and you need a point in time recovery
1. You must check that your database is in full recovery model and valid FULL backup is already taken.
2. Create a transaction log backup by using graphical interface or just executing following tsql.
3.Restore your full database backup BUT with RESTORE WITH NORECROVERY option
BUT up to your desired time. In this example we will restore our Transaction Log file up to 3:30PM.
*/

-- Take Full Backup

BACKUP DATABASE [AdventureWorks] TO DISK = N'E:\EmergencyFullBackup.bak'
WITH INIT,STATS = 10
GO

-- Take TLog Backup

BACKUP LOG [AdventureWorks] TO DISK = N'E:\EmergencyLogBackup.trn' WITH
NOFORMAT, NOINIT, NAME = N'AdventureWorks-Transaction Log  Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO

-- Restore FullBackup with NORECOVERY Option

 RESTORE DATABASE AdventureWorks2008R2
 FROM DISK = 'E:\EmergencyFullBackup.bak'
 WITH NORECOVERY;
GO

-- Restore TLog Backup upto your desired time

RESTORE LOG [AdventureWorks]
FROM DISK = 'E:\EmergencyLogBackup.trn'
WITH RECOVERY,
STOPAT = 'Jan 03, 2011 03:30:00 PM'
GO

Table Backup

USE AdventureWorks
GO
DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
-- If i need to create CSV file Product table then
SET @table = 'Production.Product'
SET @file = 'D:\BCP_OUTPUT\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112)+ '.csv'
SET @cmd = 'bcp "AdventureWorks.' + @table + '" out "' + @file + '" -S. -T -c -t,'
EXEC master..xp_cmdshell @cmd

SQL Server Schema Transfer

-- Schema Transfer

SELECT 'ALTER SCHEMA Testing TRANSFER HumanResources.'+name
FROM sys.objects
WHERE type IN ('U','V','P','Fn')
AND SCHEMA_NAME(SCHEMA_ID) = 'HumanResources'

Find brute force attempts to guess a password

BEGIN

CREATE TABLE #ErrorLog (
LogDate datetime,
ProcessInfo varchar(250),
[Text] varchar(8000)
)


--read the current error log
INSERT INTO #errorLog (LogDate, ProcessInfo, [Text])
EXEC sp_readerrorlog 0, 1 --0 = current(0), 1 = error log


--find brute force attempts to guess a password
SELECT
REPLACE(right([Text],CHARINDEX(' ', reverse([Text]))-1), ']', '') AS IP, SUBSTRING([Text], CHARINDEX('''', [Text]) + 1,
CHARINDEX('.', [Text]) - CHARINDEX('''', [Text]) - 2  ) AS [User],
COUNT(LogDate) AS [Number of login attempts],
MIN(LogDate) AS [Attack started],
MAX(LogDate) AS [Attack ended],
DATEDIFF(MINUTE, MIN(LogDate),
MAX(LogDate)) AS [Attack duration in minutes],
CAST(CAST(COUNT(LogDate) AS DECIMAL(18,2))/ISNULL(NULLIF(DATEDIFF(MINUTE,MIN (LogDate),
MAX(LogDate)),0),1) AS DECIMAL(18,2)) AS [Attack intensity - Login attempts per minute]

FROM #errorLog

WHERE
--limit data to unsuccessful login attempts in the last 24 hours
ProcessInfo = 'Logon'
and [Text] like 'Login failed for user%'
and DATEDIFF(hour, LogDate, GETDATE()) <= 24

group by [Text]
having COUNT(LogDate) > 3 --filter out users just typing their passwords incorrectly

ORDER BY [Number of login attempts] DESC,[Attack ended] DESC

--clean up temp tables created
--DROP TABLE #ErrorLog


END