Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate Backup without using SQLServerAgent Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 1:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 6:30 AM
Points: 250, Visits: 743
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.
Post #1351448
Posted Wednesday, August 29, 2012 1:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 5,132, Visits: 4,922
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1351449
Posted Wednesday, August 29, 2012 2:48 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, January 31, 2014 2:56 AM
Points: 483, Visits: 256
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.
Post #1351473
Posted Wednesday, August 29, 2012 2:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 119, Visits: 1,038
you can write a sql script to backup and clear backup files, then in schedule task, use sqlcmd to call this script.

[li][/li]
Post #1351480
Posted Wednesday, August 29, 2012 7:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:11 AM
Points: 961, Visits: 4,974
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 ************************************* >> %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
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 ************************************* >> %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
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
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.
Post #1351604
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse