Batch File to Backup SQL Server

Update 10/22/2010: Updated to rename the backup file to include the current date before copying

No budget for a Backup Exec SQL agent license? Save the following as a batch file and run it as a scheduled task to back up a database. The following items must be changed in this script to appropriate values:

1. -SMYSERVERNAME - MYSEVERNAME needs to be the SQL server name (note there is no space between -S and MYSERVERNAME)
2. MYSQLINSTANCE - this is the SQL instance name
3. MYDATABASENAME - the name of the database to be backed up
4. BACKUPDIRECTORY - the directory name on the SQL server to put the backup file in
5. FILENAME.bak - this is the name of the backup file
6. MyBackupServer - name of the remote server to map a drive

@ECHO OFF

SET CMD_PATH=%SYSTEMROOT%\system32\cmd.exe

%CMD_Path% /c ""C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql" ^ -E ^ -SMYSERVERNAME\MYSQLINSTANCE ^ -Q"BACKUP DATABASE MYDATABASENAME TO DISK = ^ 'C:\BACKUPDIRECTORY\FILENAME.bak' WITH INIT"

REM Rename the file to today's date

for /f "tokens=1-5 delims=/ " %%d in ("%date%") do rename D:\Backups\vCenter-backup.bak vCenter-backup%%e-%%f-%%g.bak


REM Map a drive to a remote computer to copy it off host

%CMD_PATH% /c "net use t: "\\MyBackupServer\d$\Services Backups\Backup""

REM Robocopy is a free tool from Microsoft to copy and replace the file

%CMD_Path% /c "robocopy.exe "C:\BACKUPDIRECTORY" "T:" /E /ZB /COPYALL ^ /LOG:"c:\BACKUPDIRECTORY\Backup.log"

REM remove the drive mapping

%CMD_PATH% /c "net use t: /delete"