SQL Server Scripts
  

---

Backup all SQL Server databases

Here is the script that will allow you to backup each database within your instance of SQL Server. You will need to change the @path to the appropriate backup directory.

File Naming Format DBname_YYYYDDMM.BAK

DECLARE @nameVARCHAR(50)

-- database name

DECLARE @pathVARCHAR(256)

-- path for backup files

DECLARE @fileNameVARCHAR(256)

-- filename for backup

DECLARE @fileDateVARCHAR(20)

-- used for file name

-- specify database backup directory

SET @path= 'C:Backup'

-- specify filename format

SELECT @fileDate= CONVERT(VARCHAR(20),GETDATE(), 112)

DECLARE db_cursorCURSOR

FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ( 'master', 'model','msdb', 'tempdb' ) -- exclude these databases

OPEN db_cursor

FETCHNEXT FROM db_cursorINTO @name

WHILE@@FETCH_STATUS = 0

BEGIN

SET @fileName = @path+ @name + '_' + @fileDate +'.BAK'

BACKUP DATABASE @nameTO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

File Naming Format DBname_YYYDDMM_HHMMSS.TRN

 DECLARE @nameVARCHAR(50)-- database name

DECLARE @pathVARCHAR(256)-- path for backup files

DECLARE @fileNameVARCHAR(256)-- filename for backup

DECLARE @fileDateVARCHAR(20)-- used for file name

SET @path= 'C:Backup'

SELECT @fileDate= CONVERT(VARCHAR(20),GETDATE(),112)

+ '_'

+ REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursorCURSOR FOR

SELECT name

FROMmaster.dbo.sysdatabases

WHERE nameNOT IN('master','model','msdb','tempdb')

AND DATABASEPROPERTYEX(name,'Recovery') IN ('FULL','BULK_LOGGED')

OPEN db_cursor

FETCHNEXT FROM db_cursorINTO @name

WHILE@@FETCH_STATUS = 0

BEGIN

SET @fileName = @path+ @name + '_' + @fileDate +'.TRN'

BACKUP LOG @nameTO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor 

---

Shrinking ldf

Here is the script that will allow you to shrink ldf.

--change the recovery mode from full to simple

ALTERDATABASE <DB_Name> SETRECOVERY SIMPLE

DBCC SHRINKDATABASE(<DB_Name> , 5)

DBCC SHRINKFILE

(

<DB_Log>,TRUNCATEONLY

)

--Switching back to full recovery

ALTERDATABASE <DB_Name> SETRECOVERY FULL

 

---