'*****************************************************
'		Script Written by Larry Heintz
'		Dec 2007 www.windowsadminscripts.com
' This script will backup all SQL Dbases on SQL Server.
' You can also have email sent after backups are done
' Also you can copy local backups to remote server
' and then delete local backups.
'
' usage cscript sqlbackup.vbs *will use local pc for server
' or
' usage cscript sqlbackup.vbs /computer:[sql server name]
'*****************************************************
Dim args,strDBServerName,strBackupPath
Dim ToAddress,FromAddress
Set args = Wscript.Arguments.Named
strBackupPath = ""			'Path to send Backup files to local machine
strRBUP = ""				'Path to send Backup files to Remote machine
ToAddress = ""				'Email Address to for Email Confirmation
FromAddress = ""			'Email Address from for Email Confirmation
SendEmail = "True"			'True to send email False not to send
EmailComponent = "CDOSYS"	'CDOSYS or CDONTS

if args.Item("computer") <> "" then
	strDBServerName = args.Item("computer")
else
	strDBServerName = getComputer()
end if

Call getDbases (strDBServerName)

Sub getDbases (sqlserver)
Dim objSQLServer,colDatabases,starttime,objDatabase,count
starttime = now()
count = 0
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
	objSQLServer.LoginSecure = True
	objSQLServer.Connect strDBServerName
Set colDatabases = objSQLServer.Databases
For Each objDatabase In colDatabases
	wscript.echo objDatabase.Name
	count = count + 1
	backupDBase sqlserver,objDatabase.Name
Next
Set colDatabases = nothing
Set objSQLServer = nothing

'Uncomment line below if you wish to move backup files to a remote machine and then delete local backups
'Call getDirListing

endtime = now()
if SendEmail = True then
	emailSend starttime,endtime,count
end if
End Sub

Sub backupDBase (sqlserver,dbname)
Dim objBackup,objSQLServer
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect sqlserver
Set objBackup = CreateObject("SQLDMO.Backup")
	'setting the properties
	objBackup.BackupSetName = dbname & "BackUp"
	objBackup.Database = dbname
	objBackup.Action = 0	'0 = Full backup 1 = Differential 2 = Specified files only 3 = Log backup
	objBackup.BackupSetDescription = dbname & "Backup"
	objBackup.Files = strBackupPath & "\" & dbname & ".bak"
	objBackup.TruncateLog = 2 '2=Truncate
	objBackup.Initialize = True
	
	'backing up the database
	if dbname <> "tempdb" then
		objBackup.SQLBackup objSQLServer
	End if
	
	'disconnecting from the server
	objSQLServer.disconnect

set objBackup = Nothing
set objSQLServer = nothing

wscript.echo "backup for " & dbname & " done"
End Sub

Sub emailSend (starttime,endtime,count)
Dim Mailer,Message,Subject

if EmailComponent = "CDOSYS" then
	Set Mailer = CreateObject("CDO.Message")
	Mailer.TextBody = Message
elseif EmailComponent = "CDONTS" then
	Set Mailer = CreateObject("CDONTS.NewMail")
	Mailer.Body = Message
else
	wscript.echo "Please make sure you are using either CDOSYS or CDONTS for EmailComponent variable"
	wscript.quit
end if

Message = "===============================================" & VbCrLf
Message = Message & "SQL Server Backup for " & strDBServerName & " has been completed" & VbCrLf
Message = Message & "Number of Dbases: " & count & VbCrLf
Message = Message & "Start Date/Time: " & starttime & " " & VbCrLf
Message = Message & "End Date/Time: " & endtime & " " & VbCrLf
Message = Message & "===============================================" & VbCrLf
Subject = "SQL Server Backup for " & strDBServerName & " has been completed"
Mailer.Subject = Subject
Mailer.To = ToAddress
Mailer.From = FromAddress
Mailer.Send
set Mailer = Nothing
End Sub

Sub getDirListing
Dim fso,flog,temp,flname
Set fso = CreateObject("Scripting.FileSystemObject") 
for each flog in fso.GetFolder(strBackupPath).files
	flname = flog.name
	set fsoobj = fso.Getfile(strBackupPath & "\" & flog.name)
		fsoobj.copy(strRBUP & "\" & flog.name)
		wscript.echo flog.name & " copied to remote server"
		fsoobj.delete
		wscript.echo flname & " local copy has been deleted"
	set fsoobj = nothing
Next
Set fso = nothing
End Sub