BlackBerry Forums Support Community - Script to perform a scheduled BESMgmt DB Backup

Page 1 of 2

Show 100 post(s) from this thread on one page

BlackBerry Forums Support Community (http://www.blackberryforums.com/index.php)

- BES Admin Corner (http://www.blackberryforums.com/forumdisplay.php?f=21)

- - Script to perform a scheduled BESMgmt DB Backup (http://www.blackberryforums.com/showthread.php?t=98859)

hdawg

10-07-2007 08:32 AM

Script to perform a scheduled BESMgmt DB Backup & FTP to remote server

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 code 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-2007-10-07-Sun-09-25.bak.

I needed something quick for customers that used MSDE, and I wanted the filename to be as descriptive as possible. You can schedule this, and then use a regular flat-file backup agent to pick up the database backup, and no need for a SQL Agent on the server.

jviola_simi

10-07-2007 11:23 AM

Thank You!!!! Much better than the .bat I was using. Now all i need to do is ftp the file to a server that is backed up on TSM. I'm having problem with the command line doing it unattended.

hdawg

10-07-2007 11:43 AM

Quote:

Originally Posted by jviola_simi (Post 696002)

Thank You!!!! Much better than the .bat I was using. Now all i need to do is ftp the file to a server that is backed up on TSM. I'm having problem with the command line doing it unattended.

Try this ... here is some sample data:

Username: bob
Password: monkey
Hostname: server.local
Directory to upload file on ftp server: my/stuff/here
Local directory to fetch from: C:\BlackBerryDBBackup
files to send: BESMgmt*.bak

create a file called script.ftp with the following contents:

Code:

bob

monkey

binary

prompt n

cd my/stuff/here

lcd C:\BlackBerryDBBackup

mput BESMgmt*.bak

quit

Then create a file in the same directory as script.ftp called ftpit.cmd with the following contents:

Code:

ftp -s:script.ftp server.local

You could use the command xxcopy in your backup script to purge files older than a certain amount of days. Good luck; hope this helps.

hdawg

10-07-2007 11:44 AM

You could even redirect all output to a text file and use blat.exe to email you a report nightly; all sorts of options.

jviola_simi

10-07-2007 12:05 PM

You guys are studs!!!! Thank you for helping my lazy butt out.

cche	10-30-2007 02:30 PM

Thanks all for the tips. Much appreciated.

Che

gfisher99

06-19-2008 04:46 PM

Dug this up from the dead...

I REALLY need to get my server backed up. Its been in production for almost 3 years with no backup.. 912 users. Yikes.

Anyways, just want to know if this script will take the DB offline or anything while its running?

If someone that uses this, or OSQL in general, could let me know, I would appreciate it...

Thanks much,
Greg

gfisher99

06-19-2008 07:02 PM

Nevermind, gents. I just tried it myself on a lab machine and it works without taking anything offline.

This is a god send. Thanks to the OP!

Greg

hdawg

06-19-2008 08:53 PM

(y) glad to hear it works for you.

juwaack68

06-19-2008 09:00 PM

Wow. Three years with no backup. And I thought 8 months was long.... I just implemented this on 3 of my servers.

The Monkey rulz!

jeffro01

08-04-2008 05:24 PM

Quote:

Originally Posted by hdawg (Post 695901)

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"

Whats a good method of restore for this type of backup?

Jeff

O'dell

08-14-2008 09:51 AM

Sorry to drag this up from the dead, but the other thread this morning made me double check my backups and I just realized that when I switched to a new box and SQL 2005 from MSDE that this script quit working.

Any suggestions on how to get this to work? I just manually backed up my DB, but would like to get it automated again.

TargetIT

08-14-2008 11:02 AM

I would venture to guess that if you simply copied the task, you need to supply the credentials again in the task scheduler - the passwords don't carry over. I've even had to recreate the task from scratch for it to work.

DavidAdams

08-15-2008 05:14 AM

Not sure where the problem lies but if I backup my database using this script its 20Mb in size, give or take a wee bit, but if I backed up using the option from SQL Studio Management Express it used to be 200Mb in size.

Well it was but isn't anymore. Just backed up from SSME and it's now the same size.

What gives?

Luukr

02-22-2009 12:05 PM

How can i restore this backup ?

Thanks in advance!

TargetIT

02-22-2009 04:57 PM

Just thought I'd add that here's a freeware version with email notification as well as rotating your backups out:

Free SQL Server database backup, Zip and FTP software

Very easy to use.

jibi	02-22-2009 05:17 PM

Quote:

Originally Posted by Luukr (Post 1294679)

How can i restore this backup ?

Thanks in advance!

As the script is using OSQL to backup the database, you would use the same tool to restore it, only with a slightly different command:

OSQL -E -Q "restore database BESMgmt from disk = 'C:\BlackBerryDBBackup\backup.bak'"

...or you could restore it using Microsoft SQL Studio Express, as well.

@DavidAdams, are you using the backups were 200MB in size and not the actual database (MDF file)? The BES database starts out at 100MB, although the backups can be as small as 5MB. Another thought is that perhaps your transaction logs were truncated at some point, which would affect the backup size.

dcomiskey

02-24-2009 09:17 AM

Can anyone come up with an additional code in this script to then COPY the new backup file to another drive? I have the sript working great, but I'd like to also make a copy to a share on another server (in this case Z:) for redundancy.

I am awful at scripting, so I can't figure out how to do this.

gfisher99

02-24-2009 09:58 AM

I use robocopy (Free Utility) to copy them to another server. You can download it.

My code is below:

Code:

cd\

robocopy c:\blackberrydbbackup \\<DestinationServerName>\D$\BlackBerryBackup /E /XO

cd blackberrydbbackup

del bes*

I put this code into a file I called "MoveFilesAfterBackup.cmd" and run it as a scheduled task approx 30 minutes after my backup completes. I ran a backup first to see how long it takes, then scheduled my task accordingly.

On the destination server, I run a zdel script to delete backups that are 3+ days old.

dcomiskey

05-08-2009 02:16 PM

Very odd thing happened. For some reason this script - which was working perfectly fine for months - all of a sudden stopped working on 4/1 and hasn't run since. When I double-click on teh script the window just flashes and nothing happens. I'm not even getting any errors in the Event Viewed, which doesn't help me.

Any ideas on what to try? :?

EDIT: I'm trying to manually run osql -E in the command prompt and I'm getting "'osql' is not recognized as an internal or external command, operable program or batch file."

Uh-oh....

Edit 2: In looking back at that day, the server crashed and there's a chance that's when I re-installed BES.

All times are GMT -5. The time now is 03:35 AM.

Page 1 of 2

Show 100 post(s) from this thread on one page