Home > MSSQL, Scripting > KiXtart script to backup MSSQL database

KiXtart script to backup MSSQL database

August 22nd, 2008 Leave a comment Go to comments

Here is another quick KiXtart script I wrote to backup a Microsoft SQL database on a server. I personally run it locally on the server that contains the database I want to backup using a scheduled task. It connects to the MSSQL database using the OSQL command line utility which it runs through a shell.

When you install MSSQL on a server, the ‘PATH’ environment variable is changed to include the location of the SQL tools (the default being: C:\Program Files\Microsoft SQL Server\80\Tools\BINN). The script runs without needing to know where osql.exe is installed and passes it switches to initiate a complete backup. Once the backup has completed SQL Server Enterprise Manager reflects the new date and time of the last backup taken as though you have used its own SQL Server Backup utility.

The script executes osql.exe with 2 switches, -E and -Q.

-E = Uses windows authentication (the credentials of the currently logged on user OR if using a scheduled task to run the script the credentials specified when initially creating the task)

-Q = Runs the SQL statement and exits osql.exe

Here is the script, you will only need to modify 2 variables ($databasename and $backuplocation) to set the name of the database you want to backup and the location of where you want the backup saved:

;===============================================
;===============================================
;
; Backup MSSQL Database
;
;===============================================
;===============================================

;Convert YYYY/MM/DD to DD-MM-YYYY
$temp = @DATE
$todaysdate = SUBSTR($temp, 9, 2)
$todaysdate = $todaysdate + "-" + SUBSTR($temp, 6, 2)
$todaysdate = $todaysdate + "-" + SUBSTR($temp, 1, 4)

;Set Backup Parameters
$databasename = "MyDatabase"
$backuplocation = "c:\MSSQL Backups\"

;If backup directory doesn't exist then create it
IF NOT EXIST ($backuplocation)
MD ($backuplocation)
ENDIF

;Delete existing backup if it exists
IF EXIST ($backuplocation)
DEL ($backuplocation + "*.*")
ENDIF

;Form Backup command
$backupcommand = "osql -E -Q " + chr(34) + "BACKUP DATABASE " + $databasename + " TO DISK = '" + $backuplocation + $databasename + "-" + $todaysdate + ".bak" + "'" + chr(34)

SHELL ($backupcommand)

;===============================================

If you ever wanted to restore from this backup you could use the command line to restore it again. Here is an example restore command (remove the brackets):

osql -E -Q "RESTORE DATABASE (databasename) FROM DISK = 'c:\MSSQL Backups\(mydatabasebackup.bak)'"

You can obviously also use SQL Server Enterprise Managers own SQL Server Backup utility to restore the database as well.

Categories: MSSQL, Scripting Tags:
  1. Janeece
    July 2nd, 2011 at 12:33 | #1

    Geez, that's unbelievable. Kudos and such.

*

code