Encrypt and Decrypt in SQL Server

Its mandatory to create Master Key

Now we are creating an asymmetric key is a combination of public key and private key. A public key is used to encrypt the data and a private key to decrypt the data.

USE prasi
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Pr@S!C00l'

CREATE ASYMMETRIC KEY AsymKey   
WITH ALGORITHM = RSA_1024
go
DECLARE @EncryptedText VARBINARY(128)
DECLARE @Text VARCHAR(128)
DECLARE @DecryptedText VARCHAR(MAX)
SET @Text='I am Super Human'
SET @EncryptedText=ENCRYPTBYASYMKEY(ASYMKEY_ID(N'AsymKey'),@Text)
SET @DecryptedText=DECRYPTBYASYMKEY (ASYMKEY_ID(N'AsymKey'),@EncryptedText)
SELECT @Text AS 'TextToEncrypt',@EncryptedText AS 'EncryptedText',@DecryptedText AS 'DecryptedText'

we will create an symmetric key and encrypt and decrypt a string using this key.

DECLARE @PWD varchar(30) = 'Pr@S!C00l'

CREATE CERTIFICATE TESTCERT
ENCRYPTION BY PASSWORD = 'Pr@S!C00l'
WITH SUBJECT ='TEST CERTIFICATE',
START_DATE='12/10/2017', 
EXPIRY_DATE='11/01/2018'

DROP CERTIFICATE TESTCERT

CREATE SYMMETRIC KEY TestSymKey               
WITH ALGORITHM =AES_256
ENCRYPTION BY CERTIFICATE TestCert
GO

OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY CERTIFICATE TestCert 
WITH PASSWORD ='Pr@S!C00l'
GO


DECLARE @Text VARCHAR(MAX)
SET @Text = 'I am Super Human'

DECLARE @EncryptedText VARBINARY(128)
SET @EncryptedText = (SELECT ENCRYPTBYKEY(KEY_GUID(N'TestSymKey'),@Text))

DECLARE @DecryptedText VARCHAR(MAX)

SET @DecryptedText = (SELECT CONVERT(VARCHAR(MAX),DECRYPTBYKEY(@EncryptedText)))


SELECT @Text AS 'TextToEncrypt',@EncryptedText AS 'EncryptedText',@DecryptedText AS 'DecryptedText'

DROP SYMMETRIC KEY TestSymKey

GO 

CMD to hide partion hdd from explorer

Type below commands in cmd

diskpart
list disk
sel disk *
list volume
sel volume *
remove letter **
To Reassign type : assign letter **
* represents the number
** represents the Drive letter

To Encrypt files via Command prompt

Go to location and type below command

C:\Users\Desktop\New folder>CIPHER /E

cipher commands

    /D        Decrypts the specified files or directories.
    /E        Encrypts the specified files or directories. Directories will be
              marked so that files added afterward will be encrypted. The
              encrypted file could become decrypted when it is modified if the
              parent directory is not encrypted. It is recommended that you
              encrypt the file and the parent directory.

Advantages is :  other login users cant access and not accessible in other systems too.
    

Windows Command to know Wifi Password of already connected wifi device.

Type below commands in administrative mode in cmd:

   netsh wlan show profile

        will show already connected devices.

then

  netsh wlan show profile ***** key=clear

change ***** from 1st command value

Using JOIN in UPDATE Statement

CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'
GO
-- Create table2
CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
--WHERE t1.Col1 IN (21, 31)

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

DROP TABLE Table1
DROP TABLE Table2

Pokemon Emerald Ruby Red Gold emulation with Visual Boy Advance

1. Pokemon Emerald Ruby Red Gold emulation with Visual Boy Advance

Click the above link

SQL TRANSACTION LOG SHRINK/TRUNCATE

use [DBNAME]
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE
declare @fileId as int = (select file_id from sys.database_files where name like '%log%')

DBCC SHRINKFILE(@fileID, EmptyFile)
DBCC SHRINKDATABASE ([DBNAME], 0, TRUNCATEONLY);

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

IN THIS YOU CANT EASILY GET DB LOG NAME IN @FILEID



For Multiple Database, We can use Cursor like below

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE @Command3 nvarchar(500)
DECLARE database_cursor CURSOR FOR 
SELECT name 
FROM MASTER.sys.sysdatabases

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
IF (@DB_Name <> 'master' AND @DB_Name <> 'tempdb' AND @DB_Name <> 'model' AND @DB_Name <> 'msdb') 
 BEGIN
--SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM ' +  @DB_Name + '.sys.sysfiles SF'
     SELECT @Command ='ALTER DATABASE [' + @DB_Name + '] SET RECOVERY SIMPLE'
     EXEC sp_executesql @Command
     Select @Command = 'use [' + @DB_Name + '] ' 
     Select @Command2 = @Command + 'SELECT name ,size/128.0 - CAST(FILEPROPERTY(name,' + ''''+  'SpaceUsed'+ '''' +') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files'
     select @Command3 = 'declare @fileId as int = (select file_id from sys.database_files where name like ''%log%'');DBCC SHRINKFILE(@fileID, EmptyFile);DBCC SHRINKDATABASE ([' +@DB_Name + '], 0, TRUNCATEONLY);'
     EXECUTE sp_executesql @Command2;EXECUTE sp_executesql @Command3;EXECUTE sp_executesql @Command2 --exec (@Command2)
  End
     FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor

Batch file to delete Folder and files by setting the parameter of the folder

Sample : https://www.dropbox.com/s/cvwltunpvwqxa3l/call%201.bat%20Brighton_v45.bat?dl=0


Code:
@echo off
setlocal EnableDelayedExpansion
cls
ECHO You are running: %0 %1 %date% %time%
REM set param1=%~1
set param1=Brighton_v45
set MYDIR=C:\
set tempPath=c:\%param1%\src\ProfilingSetup
set f1="%tempPath%\Default Configuration"
set f2="%tempPath%\Script Files\*.*"
set srcfile="%tempPath%\Script Files\setup.rul*"
set destfile="%tempPath%\setup.rul*"
set srcfiles="%tempPath%\String1033.txt*"
set destfiles="%tempPath%\Script Files\String1033.txt*"
for /F %%i in ('dir /B/D %MYDIR%') do call :Foo %%i
goto END

:Foo
set z=%1
if "%z%"=="%param1%" (
REM echo z set
echo %param1%
@RD /S /Q %f1%
xcopy /f /y %srcfile% %destfile%
del /s /q %f2%
xcopy /f /y %destfile% %srcfile%
xcopy /f /y %srcfiles% %destfiles%
del /q %tempPath%
xcopy /f /y %destfiles% %srcfiles%
del /q %destfiles%
)
goto :eof


:End

Shrink Log Space in SQL

DBCC LOGINFO(DB_NAME)
BACKUP LOG [DB_NAME] TO DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL\Backup\DB_NAME.bak'
DBCC SHRINKFILE(NEWDATACOLLECT_Log)--right click db goto properties then files menu there you can get log name
DBCC LOGINFO(DB_NAME)