A simple, yet very powerful script that will backup all of the databases on your Microsoft SQL Server to flat file for easy backup and restore. Additional features, such as limiting the number of archive files to keep and copying the latest file to a different directory, are also available.

Configuration

Each and everytime I’ve configured this program, I’ve installed both the script and the compression executable in a directory named c:\sqlbackup. This has always seemed tow ork best for me as everything is in one location and can easily be managed.

Here are the configuration options, all of which are located at the top of the script:

[BackupDirectory]

The directory where you want the archived database files to be saved. The directory listed here will be automatically created; if multi-directory path is configured and the parent directories don’t exist an error will be thrown. So if the default value is used, make sure the c:\sqlbackup directory exists.

Defaults to ‘c:\sqlbackup\db’

[MaxBackupAmount]

The maximum number of compressed database archives to keep.

Defaults to 14

[DatabaseServer]

The name of the database server that is being backed up. The script can only output files to the same machine that SQL is running on so this should either be ‘localhost’ or the server’s name.

Defaults to ‘localhost’

[DatabaseUser]

The username for the database administrator.

Defaults to ’sa’

[DatabasePassword]

Should be self explainitory. You”re going to need to change this.

Defaults to ‘passwd’

[ZipPath]

The full path and any additional command-line options to your compression program. I’ve always used PKZIP, although I’m assuming any other compression program would work.

Defaults to ‘c:\sqlbackup\pkzip25.exe -add -silent’

[RunDBDump]

If set to True, the databases are dumped to flat-file. I’m not really certain why I put this option in back then…

Defaults to True

[RunCompression]

If set to True, all of the database flat-files are compressed into one archive file.

Defaults to True

[IBackupBackupPath]

This can be the full path where you would like the current archive file to be copied. This was useful to me at one point becuase the online backup company we used at the time – this allowed me to always backup the current version to their web site on a nightly basis, without running into some rather stupid restrictions of their service.

Defaults to ” (empty, not used)

That’s about it. I’ve used this script for many and it’s aways performed perfectly!

'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

Download this code: sqlbackup.vbs

This script is old – I’m talking like year 2000 maybe even 1999 old – and has been tested of versions of Microsoft SQL Server since 2000.