SQL Large log file size

There are instance where the log files have grown up too large for example 200 gb of file size, in that case, here are the following steps to be done to reduce the log file.
1.Check the database options if it is simple change it to Full.
2. Backup the database using the below script.
--****************************************************
--Backup database script
--****************************************************
BACKUP DATABASE [lmkdw] TO
DISK = N'd:\lmkdw_backup.bak' WITH NOFORMAT,
INIT, NAME = N'lmkdw_backup', SKIP, REWIND, NOUNLOAD,
STATS = 10
3. Then backup the log file using the below script
--****************************************************
--Backup database script
--****************************************************
BACKUP LOG [apacdw] TO DISK = N'd:\lmkdw.trn'
WITH NOFORMAT, INIT, NAME = N'lmkdw-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
go
4. Now shrink the log file using the below script
DBCC SHRINKFILE (N'lmkdw_Log' , 786)
5. Finall turn the database options back to simple or based on your needs.

Robocopy Tool

Robocopy is a awesome tool to copy backup files. Here is the syntax to copy files.
robocopy "Z:\DATABASE\FULLBACKUP" "\\xxx.xxx.x.xxx\backups\FullBackup" /zb /copyall /purge /MIR

robocopy "source" "destination"

Copy options :
/S :: copy Subdirectories, but not empty ones./E :: copy subdirectories, including Empty ones./LEV:n :: only copy the top n LEVels of the source directory tree.
/Z :: copy files in restartable mode./B :: copy files in Backup mode./ZB :: use restartable mode; if access denied use Backup mode./EFSRAW :: copy all encrypted files in EFS RAW mode.
/COPY:copyflag[s] :: what to COPY for files (default is /COPY:DAT).(copyflags : D=Data, A=Attributes, T=Timestamps).(S=Security=NTFS ACLs, O=Owner info, U=aUditing info).
/DCOPY:T :: COPY Directory Timestamps.
/SEC :: copy files with SECurity (equivalent to /COPY:DATS)./COPYALL :: COPY ALL file info (equivalent to /COPY:DATSOU)./NOCOPY :: COPY NO file info (useful with /PURGE).
/SECFIX :: FIX file SECurity on all files, even skipped files./TIMFIX :: FIX file TIMes on all files, even skipped files.
/PURGE :: delete dest files/dirs that no longer exist in source./MIR :: MIRror a directory tree (equivalent to /E plus /PURGE).
/MOV :: MOVe files (delete from source after copying)./MOVE :: MOVE files AND dirs (delete from source after copying).

Copying folders, subfolders and files

Copying folders, subfolders and files from one location to another and over writing it.Very useful for backing up of files.

Const OverWriteFiles = TrueSet objFSO = CreateObject("Scripting.FileSystemObject")objFSO.CopyFolder "c:\vijay\t1" , "c:\vijay\t2" , OverWriteFiles
set objfso = nothing

set date field null

to set the date field null
update table name set date = null

VB Script file to delete backup files

Create vbs file Vbscript file with the below code to delete backup files which are older than 3 days


Dim FsoDim Directory
Dim Modified
Dim Files Set Fso = CreateObject("Scripting.FileSystemObject")
Set Directory = Fso.GetFolder("E:\transfer\TulsaBackup\goes")
Set Files = Directory.Files
For Each Modified in FilesIf DateDiff("D", Modified.DateLastModified, Now) > 3 Then Modified.Delete
Next