MS SQL Server Backup Script

The below script works in my SQL Server 2000 and should work on newer version of SQL server.

For Daily Complete Backup

BACKUP DATABASE [MyDB]

  TO  DISK = N'X:\RestoreData\MyDBBackup.bak'

   WITH  INIT ,  NOUNLOAD , 

   NAME = N'MyDB backup',  NOSKIP ,  STATS = 10,  NOFORMAT

Hourly Transaction Log Backup

DECLARE @CURDATE varchar(20), @SQL Varchar(8000), @File varchar(40), @ndate datetime

set @ndate = getdate()

SET @curdate = replace(replace(replace(convert(varchar(16), @ndate, 120),'-',''),':',''),' ','')

SET @File = 'ICCPRD_tlog_' + @curdate + '.TRN'

SET @SQL = 'BACKUP LOG MyDBTo Disk = ''E:\TransLog\MyDB\' + @file + ''''

print @SQL

EXEC (@SQL)

Housekeeping for TranLog Backup Files (VBScript)

Option Explicit

on error resume next

Dim oFSO

Dim sDirectoryPath

Dim oFolder

Dim oFileCollection

Dim oFile

Dim iDaysOld

Dim sDir

iDaysOld = 2

Set oFSO = CreateObject("Scripting.FileSystemObject")

sDirectoryPath = "E:\TransLog\ICCPRD"

Set oFolder = oFSO.GetFolder(sDirectoryPath)

Set oFileCollection = oFolder.Files

For each oFile in oFileCollection

 If oFile.DateLastModified < (Date() - iDaysOld) Then

  oFile.Delete(True)

 End If

Next

Set oFSO = Nothing

Set oFolder = Nothing

Set oFileCollection = Nothing

Set oFile = Nothing