[hdawg]

@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]

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]

Originally Posted by jviola_simi

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:

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:

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]

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]

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

[cche]

Thanks all for the tips. Much appreciated.

Che

[gfisher99]

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]

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]

glad to hear it works for you.

[juwaack68]

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]

Originally Posted by hdawg

@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.

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

Jeff

[O'dell]

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]

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]

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]

How can i restore this backup ?

Thanks in advance!

[TargetIT]

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]

Originally Posted by Luukr

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]

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]

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

My code is below:

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]

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.