Backup databases from MS SQL Server 2008 Express database thru batch script

Question

I need batch script to backup selected databases from Microsoft SQL 2008 Express Edition. Unfortunately MS SQL Server 2008 Express edition lacks of visual database backup tools called Maintenance in MS SQL 2008 Standard, thus We need a batch script to backup databases.

Answer 1

I wrote an universal script for backup selected databases from MS Sql Server Express. Script loops thru an array of databases and generates separate .bak for every database. Backup filename starts with database name and ends with time and date. Events are logged in log.log file, including database backup task details, so non of existing backups are overwrited. Also some minor details, like backup start time.

In this script you need to define databases array that You want to backup, sa user/password, and SQL Server and instance. After these modifications all need to do, is invoke batch file from task scheduler.

The script code:

@echo off

::databases array
set baza[0]=DATABSE_1
set baza[1]=DATABSE_2

::some of variables
set var=%usersa%
set var=%passsa%
set var=%instance%
set "x=0"
set hr=%TIME: =0%
set hr=%hr:~0,2%
set min=%TIME:~3,2%
set sec=%TIME:~6,2%
set czas=%hr%%min%%sec%

::database access data and instance
set usersa=sa
set passsa=sa
set instance=HOSTSERVER\SqlServerName
set backupath=E:\SQL_BACKUP\

echo Backup Start %date%_%czas%>>log.log
echo.>>log.log%%echo.>>log.log%%echo.>>log.log
:loop
if defined baza[%x%] (
    call osql -S %instance% -U %usersa% -P %passsa% -d %%baza[%x%]%% -Q "BACKUP DATABASE %%baza[%x%]%% TO DISK = '%backupath%%%baza[%x%]%%_%date%_%czas%.bak'" >>log.log
    set /a "x+=1"
    GOTO :loop
)
echo.>>log.log%%echo.>>log.log%%echo.>>log.log