Creating a SQL scheduled backup when LX backup doesn't work Follow

Overview

There are times when the GlasPacLX backup will not run.  Backups still need to be run, but unless the customer has a full version of SQL, they won't be able to setup Maintenance Jobs.  The following directions are meant to assist with setting up a SQL backup script and launching it from the Windows scheduler.

Note:  This script will create 7 days of backups and then will delete the oldest backup once the 8th has been written to the folder.  Make sure the customer understands that they need to be copying these files for off-site storage.

 

The SQL Script

The first thing that needs to be done is to setup an executable batch file (.bat) that will call SQL to run the backup.

The following script can be used to accomplish this task.  Make sure to replace ALL red text (including the <> symbols) with the actual customer server entries referenced.  (i.e. <SERVER\INSTANCE> should be replaced with the server and instance name like MYServer\GTS)

 

echo -- Backup Database --
set SERVERNAME=<SERVER\INSTANCE>
set DATABASENAME=<DATABASE>
set DATESTAMP=%date:~-4,4%%date:~-10,2%%date:~-7,2%-%time:~-11,2%%time:~-8,2%%time:~-5,2%
set BACKUPPATH=<PATH>
set BACKUPFILENAME=%BACKUPPATH%\%DATABASENAME%-%DATESTAMP%.BAK
sqlcmd -S %SERVERNAME% -Usa -PUN0tN0This! -d master -Q "BACKUP DATABASE %DATABASENAME% TO DISK = N'%BACKUPFILENAME%' WITH INIT, NOUNLOAD, NAME = N'%DATABASENAME% %DATESTAMP% Backup', NOSKIP, STATS = 10, NOFORMAT"
if %errorlevel% == 0 goto cleanup
exit

:cleanup
forfiles /P %BACKUPPATH% /M *.BAK /D -7 /C "cmd /c del /F @path"
exit

 

Copy this script into Notepad, and save it somewhere safe.  Change the .txt to .bat to make it an executable file.

Scheduling the Job

Open Administrative Tools from Control Panel.  Select Task Scheduler.

Click Create Basic Task

Name the new task (eg. SQL Backup)

Select Daily on the Task Trigger screen

Set the Time for the task to run

Select Start a program from the Action screen

Click the Browse button, and select your new batch file

Click the Open the Properties dialog. . . check box and flick Finish

Select Run whether user is logged on or not

Click the Change User or Group button and select the administrator user account

Click OK, and enter the password for the Administrator account

Have more questions? Submit a request

Comments

Powered by Zendesk