Script to perform a scheduled BESMgmt Database backup

hdawg · #1 (**permalink**) 01-11-2009, 08:26 PM

Check this out ...

Backup the database and give it a unique name

Code:

@for /f "Tokens=1-4 Delims=/ " %%i in ('date /t') do @set dt=%%l-%%j-%%k-%%i
@for /f "Tokens=1" %%i in ('time /t') do @set tm=-%%i
@set tm=%tm::=-%
@set dtt=%dt%%tm%
@echo Copying backup set: %dtt%.
@echo Please wait...

@if not exist C:\BlackBerryDBBackup mkdir C:\BlackBerryDBBackup
@cd /D C:\BlackBerryDBBackup
@OSQL -E -Q "backup database BESMgmt to disk = 'C:\BlackBerryDBBackup\backup.bak'"
@ren backup.bak "BESMgmt-%dtt%.bak"

Save the script as BESMgmt-backup.cmd and schedule it as a task to run on your BES as BESAdmin. It will output a file as such: BESMgmt-2009-01-11-Sun-19-25.bak. You can schedule this and depending on where you have it save (you could have it save to a mapped network drive / other location ... or even use something like robocopy with the /MIR switch to sync the contents of the directory with one on a network share) ... this is actually what I do in situations where the server the database is on doesn't get backed up daily.

Joolie · #2 (**permalink**) 01-11-2009, 08:34 PM

I use this on 3 of our servers and it works wonderfully

Sherrera · #3 (**permalink**) 01-15-2009, 08:42 PM

Great script, I just used it and it works great on my 4.1.6 BES. While I am here I am also looking for a script that would delete the files that are more then three days old for example. Anyone have any tips or hints of where I could find such a script? Again thanks for the awsome script.

hdawg · #4 (**permalink**) 01-15-2009, 10:02 PM

Quote:

Originally Posted by Sherrera

Great script, I just used it and it works great on my 4.1.6 BES. While I am here I am also looking for a script that would delete the files that are more then three days old for example. Anyone have any tips or hints of where I could find such a script? Again thanks for the awsome script.

What are you looking to delete? Anything in specific? Old log files? or just anything?

Joolie · #5 (**permalink**) 01-15-2009, 10:03 PM

Format c:/...?

Sherrera · #6 (**permalink**) 01-16-2009, 12:12 PM

Sorry, I want to be able to Schedule the deletion of the old backup files that are older then say 2 or 3 days. That why the daily backups wont fill up the HD someday.

Example:
I now have 2 backup files using this nameing convention
BESMgmt-Year-Month-Day-DayofTheWeek-Time.bak
these are backed up to the folder
D:\BlackBerryDBBackup\
We then save the files to tape everynight.
So I only really need maybe 2 or 3 days worth at any given time.
However I dont want to have to manual delete the files from the folder.
so is there a way to also automate that process?

inphektion · #7 (**permalink**) 01-16-2009, 09:55 PM

here is a mysqldump backup dos script i use as a 5 logrotate and zip it up at the end. you can pair it down to logrotate 4,3,2 etc. whatever. Its a decent enough template to use.

Code:

@echo off

:ONE1
IF EXIST C:\folder\file1.bak goto TWO2
mysqldump --opt dbname > "C:\folder\file1.bak"
IF EXIST C:\folder\file2.bak del C:\folder\file2.bak
goto:7zipit

:TWO2
IF EXIST "c:\folder\file2.bak" goto THREE3
mysqldump --opt dbname > "c:\folder\file2.bak"
IF EXIST "c:\folder\file3.bak" del c:\folder\file3.bak
goto:7zipit

:THREE3
IF EXIST "c:\folder\file3.bak" goto FOUR4
mysqldump --opt dbname > "c:\folder\file3.bak"
IF EXIST "c:\folder\file4.bak" del c:\folder\file4.bak
goto:7zipit

:FOUR4
IF EXIST "c:\folder\file4.bak" goto FIVE5
mysqldump --opt dbname > "c:\folder\file4.bak"
IF EXIST "c:\folder\file5.bak" del c:\folder\file5.bak
goto:7zipit

:FIVE5
IF EXIST "c:\folder\file5.bak" goto ONE1
mysqldump --opt dbname > "c:\folder\file5.bak"
IF EXIST "c:\folder\file1.bak" del c:\folder\file1.bak
goto:7zipit

:7zipit
"c:\Program Files\7-Zip\7z.exe" a c:\zipfiles\files.7z "c:\folder"
goto:EOF

hdawg · #8 (**permalink**) 01-22-2009, 08:59 PM

Quote:

Originally Posted by Sherrera

Sorry, I want to be able to Schedule the deletion of the old backup files that are older then say 2 or 3 days. That why the daily backups wont fill up the HD someday.

Example:
I now have 2 backup files using this nameing convention
BESMgmt-Year-Month-Day-DayofTheWeek-Time.bak
these are backed up to the folder
D:\BlackBerryDBBackup\
We then save the files to tape everynight.
So I only really need maybe 2 or 3 days worth at any given time.
However I dont want to have to manual delete the files from the folder.
so is there a way to also automate that process?

Attached to this, is forfiles.exe; great little app.

Try this out. The last line will purge files 5 days old

Code:

@for /f "Tokens=1-4 Delims=/ " %%i in ('date /t') do @set dt=%%l-%%j-%%k-%%i
@for /f "Tokens=1" %%i in ('time /t') do @set tm=-%%i
@set tm=%tm::=-%
@set dtt=%dt%%tm%
@echo Copying backup set: %dtt%.
@echo Please wait...

@if not exist C:\BlackBerryDBBackup mkdir C:\BlackBerryDBBackup
@cd /D C:\BlackBerryDBBackup
@OSQL -E -Q "backup database BESMgmt to disk = 'C:\BlackBerryDBBackup\backup.bak'"
@ren backup.bak "BESMgmt-%dtt%.bak"
forfiles -p C:\BlackBerryDBBackup -s -m *.bak -d -5 -c "cmd /c del @file"

more information on forfiles

hayabusa · #9 (**permalink**) 09-08-2009, 12:57 PM

Quote:

Originally Posted by Mr. E

Check this out ...

Backup the database and give it a unique name

Code:

@for /f "Tokens=1-4 Delims=/ " %%i in ('date /t') do @set dt=%%l-%%j-%%k-%%i
@for /f "Tokens=1" %%i in ('time /t') do @set tm=-%%i
@set tm=%tm::=-%
@set dtt=%dt%%tm%
@echo Copying backup set: %dtt%.
@echo Please wait...

@if not exist C:\BlackBerryDBBackup mkdir C:\BlackBerryDBBackup
@cd /D C:\BlackBerryDBBackup
@OSQL -E -Q "backup database BESMgmt to disk = 'C:\BlackBerryDBBackup\backup.bak'"
@ren backup.bak "BESMgmt-%dtt%.bak"

Save the script as BESMgmt-backup.cmd and schedule it as a task to run on your BES as BESAdmin. It will output a file as such: BESMgmt-2009-01-11-Sun-19-25.bak. You can schedule this and depending on where you have it save (you could have it save to a mapped network drive / other location ... or even use something like robocopy with the /MIR switch to sync the contents of the directory with one on a network share) ... this is actually what I do in situations where the server the database is on doesn't get backed up daily.

Does this need to be ran on the SQL server or the BES server? I tried running it on my BES server but the only thing it does is make the directory for the backup. Where would I feed it the database connections if we are running a remote sql 2005 box for our DB?

hdawg · #10 (**permalink**) 09-08-2009, 10:09 PM

This needs to be run on the SQL server, or you need to add a -S SERVERNAME to the OSQL statement ... such as:

@OSQL -E -S SQL01 -Q "backup database BESMgmt to disk = 'C:\BlackBerryDBBackup\backup.bak'"

REMEMBER: You need to make sure though that you run the script as a user that has permissions on the database.

raveboy78 · #11 (**permalink**) 08-04-2011, 09:06 AM

Thanks for the great script!

I'm trying to run the backup on a separate server, using all of what you have suggested, but it's not quite working out right; was hoping if you could give some suggestions. I see that it creates the folder, but it doesn't run the backup, the cmd window pops up and then closes briefly.

Any ideas?

Follow Us On

Forum Sponsors

Google Advertisements