'Program: SQL Backup Script
'Version: 1.5
'Author: Joshua R. Cook
'Website: http://www.joshcook.net/
'Date: Old. Maybe some time in 1999 or 2000?

'// Global Settings
BackupDirectory = "c:\sqlbackup\db\"
MaxBackupAmount = 14
DatabaseServer = "localhost"
DatabaseUser = "sa"
DatabasePassword = "passwd"
ZipPath = "c:\sqlbackup\pkzip25.exe -add -silent"
RunDBDump = True
RunCompression = True
IBackupBackupPath = ""

'// Get the server's name
Set Network = CreateObject("WScript.Network")
ServerName  = LCase(Network.ComputerName)
Set Network = Nothing

'// Create the directory name that we're going to use
CurrentYear		= Year(Now)
CurrentMonth	= Month(Now)
CurrentDay		= Day(Now)
CurrentHour		= Hour(Now)
CurrentMinute	= Minute(Now)
CurrentSecond	= Second(Now)

If CurrentMonth  < 10 Then CurrentMonth  = "0" & CurrentMonth
If CurrentDay    < 10 Then CurrentDay    = "0" & CurrentDay
If CurrentHour   < 10 Then CurrentHour   = "0" & CurrentHour
If CurrentMinute < 10 Then CurrentMinute = "0" & CurrentMinute
If CurrentSecond < 10 Then CurrentSecond = "0" & CurrentSecond

DirectoryName = CurrentYear & CurrentMonth & CurrentDay & "_" & CurrentHour & CurrentMinute & CurrentSecond

'// Directory creation and verification
Set fso	= CreateObject("Scripting.FileSystemObject")

'// - Make sure root backup directory exists
If Not fso.FolderExists(BackupDirectory) Then fso.CreateFolder(BackupDirectory)

'// - Make sure we haven't passed our MaxBackupAmount
WScript.Echo "Folder Management: Start"

Set fsoFolder			= fso.GetFolder(BackupDirectory)
Set objSubFolders = fsoFolder.SubFolders
SubFolderCount    = objSubFolders.Count
DelFolderDate     = "#1/1/3000#" 'Yes, I was lazy.
DelFolderName     = ""

Do While SubFolderCount >= MaxBackupAmount

	For Each Folder in objSubFolders
	
		If Folder.DateCreated < DelFolderDate Then 
			
			DelFolderDate = Folder.DateCreated
			DelFolderName = Folder.Name
			
		End If
	
	Next

	WScript.Echo("Deleting: " & DelFolderName)
	fso.DeleteFolder(BackupDirectory & DelFolderName)
	
	SubFolderCount    = objSubFolders.Count

Loop

Set objSubFolders = Nothing
Set fsoFolder = Nothing

'// - Create new backup directory for current "this" backup/dump
If fso.FolderExists(BackupDirectory & DirectoryName) Then
	
	fso.DeleteFolder(BackupDirectory & DirectoryName)
	fso.CreateFolder(BackupDirectory & DirectoryName)
	
Else

	fso.CreateFolder(BackupDirectory & DirectoryName)

End If
WScript.Echo "Folder Management: End"

'// Dump all databases to flatfile

WScript.Echo "Database Backup: Start"
If RunDBDump Then
	
	sqlDSN = "Driver={SQL Server};Server=" & DatabaseServer & ";Uid=" & DatabaseUser & ";Pwd=" & DatabasePassword
	sqlSQL	= "SELECT name FROM sysdatabases WHERE name <> 'tempdb'"
	
	Set dbConn1 = CreateObject("ADODB.Connection")
	Set dbConn2 = CreateObject("ADODB.Connection")
	
	dbConn1.CommandTimeout = 120
	dbConn2.CommandTimeout = 120
	
	dbConn1.Open sqlDSN
	dbConn2.Open sqlDSN
	
	Set rsConn = dbConn1.Execute(sqlSQL)
	
	Do Until rsConn.BOF Or rsConn.EOF
	
		DatabaseName = rsConn("name")
		DatabaseFlat = BackupDirectory & DirectoryName & "\" & DatabaseName & ".dat"
		
		dbConn2.Execute("BACKUP DATABASE [" & DatabaseName & "] TO DISK='" & DatabaseFlat & "' WITH INIT")
		
		WScript.Echo " - Database: " & DatabaseName
	
		rsConn.MoveNext
		
	Loop
	
	Set rsConn  = Nothing
	Set dbConn1 = Nothing
	Set dbConn2 = Nothing

End If
WScript.Echo "Database Backup: End"

'// Compress all databases into a single file
If RunCompression And RunDBDump Then

	WScript.Echo "Compression: Start"

	Set Shell = CreateObject("WScript.Shell")
	Shell.Run ZipPath & " " & BackupDirectory & DirectoryName & "\" & ServerName & "_db.zip " & BackupDirectory & DirectoryName & "\*.dat", 8, True
	Set Shell = Nothing
	
	'// - Remove the flatfile database backups
	fso.DeleteFile(BackupDirectory & DirectoryName & "\*.dat")

	WScript.Echo "Compression: End"
	
	'//Copy most recent version to IBackup Path
	If IBackupBackupPath <> "" Then

		Set objFileCopy = fso.GetFile(BackupDirectory & DirectoryName & "\" & ServerName & "_db.zip")
		objFileCopy.Copy IBackupBackupPath
	
	End If

End If

'Create Verify File
Set fsoFile = fso.OpenTextFile(BackupDirectory & "msfb-verify.txt", 2, True)
fsoFile.Write Now
Set fsoFile = Nothing

'// Cleanup whatever objets are still remaining
Set fso	= Nothing

WScript.Quit 0