Automate Backup without using SQLServerAgent

Question

Post reply

Automate Backup without using SQLServerAgent

ravisamigo

SSCertifiable

Points: 7440
More actions
August 29, 2012 at 1:52 am

#393042

Dear All,
We have Express edition and need to schedule backup.
How we can automate backup without using Maintenance plans and SQLServerAgent Jobs ?
Please advise.
Thanks and Regards,
Ravi.

Ant-Green SSC Guru Points: 113498 More actions · Answer 1

Use windows task scheduler would act as the alternative to the SQL Agent.

Then you can use SQLCMD, Powershell etc to write a script which will do the backup.

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 2

The only time I had to backup an Express Edition I used the SQLMAINT tool.

See http://msdn.microsoft.com/en-us/library/ms162827(v=sql.105).aspx for reference.

DBA328 Hall of Fame Points: 3952 More actions · Answer 3

you can write a sql script to backup and clear backup files, then in schedule task, use sqlcmd to call this script.

jasona.work SSC Guru Points: 50113 More actions · Answer 4

A DOS batch file run with Windows Task Scheduler.

Here's one I came up with, it does require that you have pkzip / pkzipc / pkunzip somewhere in your path, though. Also it requires you have SQL Authentication enabled, I never tried to get it to run with Windows Authentication.

@echo off

Rem Change DBNAME and backup location to appropriate

Rem The backup location MUST EXIST

Rem THIS MUST BE RUN ON THE SQL SERVER!

Rem

rem [Setup]

rem When entering the tempdrive, tempdir, and dest values,

rem ******DO NOT******

rem use any slashes!

rem

set tempdrive=

set tempdir=

set dest=

set SQLSrvr=

set dbname=

set SQLUser=

set SQLpassword=

set zippassword=

set DaysToKeep=

rem The following will remove any : or spaces from the date, to allow

rem date-stamping the backup files

Set filedate=%date:/=%

Set filedate=%filedate: =%

Set filetime=%time::=%

set filetime=%filetime:.=%

rem Clean up database backups older than 1 week from destination media

rem To change the number of days of backup files to keep, change the DaysToKeep

rem above with a new value (higher numbers will save more backups)

%dest%

Forfiles /D -%DaysToKeep% /M *.BAK /C "cmd /c del @file"

Forfiles /D -%DaysToKeep% /M *.ZIP /C "cmd /c del @file"

%tempdrive%

cd %tempdir%

Forfiles /D -%DaysToKeep% /M *.BAK /C "cmd /c del @file"

Forfiles /D -%DaysToKeep% /M *.ZIP /C "cmd /c del @file"

rem ****************************************************************************

rem This will attempt to use Windows Authentication to run, using the User

rem Account configured in the scheduled task, or the currently logged in User

rem if double-clicking the batch file.

rem If this does not work,

rem un-rem the line: "rem sqlcmd -U %SQLuser% -P %SQLpassword% -q..."

rem and rem the line: "sqlcmd -q "USE %dbname%" -Q "BACKUP..."

rem ****************************************************************************

rem sqlcmd -U %SQLuser% -P %SQLpassword% -Q "BACKUP DATABASE %dbname% TO DISK = '%tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.BAK'"

sqlcmd -S %sqlSrvr% -Q "BACKUP DATABASE %dbname% TO DISK = '%tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.BAK'"

if not exist %tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.BAK goto bad

pkzipc -add -fast -move -dir=current -pass=%zippassword% %tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.zip %tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.BAK

xcopy %tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.zip %dest%if not exist %dest%\%dbname%_%filedate%_%filetime%.zip goto badcopy

:done

echo. >> %dest%\BackupLog.log

echo %date% - %time% >> %dest%\BackupLog.log

echo ------------------------ >> %dest%\BackupLog.log

echo Database backup completed OK >> %dest%\BackupLog.log

echo. >> %dest%\BackupLog.log

echo ************************************* >> %dest%\BackupLog.log

echo. >> %tempdrive%\%tempdir%\BackupLog.log

echo %date% - %time% >> %tempdrive%\%tempdir%\BackupLog.log

echo ------------------------ >> %tempdrive%\%tempdir%\BackupLog.log

echo Database backup completed OK >> %tempdrive%\%tempdir%\BackupLog.log

echo. >> %tempdrive%\%tempdir%\BackupLog.log

echo ************************************* >> %tempdrive%\%tempdir%\BackupLog.log

goto end

:bad

echo. >> %dest%\BackupLog.log

echo %date% - %time% >> %dest%\BackupLog.log

echo ------------------------ >> %dest%\BackupLog.log

echo Database backup encountered a problem >> %dest%\BackupLog.log

echo Check SQL Server Logs for more information >> %dest%\BackupLog.log

echo. >> %dest%\BackupLog.log

echo ************************************* >> %dest%\BackupLog.log

echo. >> %tempdrive%\%tempdir%\BackupLog.log

echo %date% - %time% >> %tempdrive%\%tempdir%\BackupLog.log

echo ------------------------ >> %tempdrive%\%tempdir%\BackupLog.log

echo Database backup encountered a problem >> %tempdrive%\%tempdir%\BackupLog.log

echo Check SQL Server Logs for more information >> %tempdrive%\%tempdir%\BackupLog.log

echo. >> %tempdrive%\%tempdir%\BackupLog.log

echo ************************************* >> %tempdrive%\%tempdir%\BackupLog.log

goto end

:badcopy

echo. >> %tempdrive%\%tempdir%\BackupLog.log

echo %date% - %time% >> %tempdrive%\%tempdir%\BackupLog.log

echo ------------------------ >> %tempdrive%\%tempdir%\BackupLog.log

echo Database backup encountered a problem >> %tempdrive%\%tempdir%\BackupLog.log

echo when copying backup file to %dest% >> %tempdrive%\%tempdir%\BackupLog.log

echo. >> %tempdrive%\%tempdir%\BackupLog.log

echo ************************************* >> %tempdrive%\%tempdir%\BackupLog.log

goto end

:end

And now, for how long it's been since I worked with this...

Glancing over it after posting, it will work with Windows Auth, by default.

:hehe: