Backup of BCM Database

  • Thread starter Remove ABCD from Email address to reply
  • Start date
R

Remove ABCD from Email address to reply

I run automatic backup software at night of all data that has changed. Each
night I close Outlook 2003 so that copies can be made. I have found out
that BCM stays tied up because the SQL server is still running. Does anyone
have a resolution that would allow me to make a backup without stopping the
SQL server?
 
A

AuditMaster

I run automatic backup software at night of all data that has changed. Each
night I close Outlook 2003 so that copies can be made. I have found out
that BCM stays tied up because the SQL server is still running. Does anyone
have a resolution that would allow me to make a backup without stopping the
SQL server?

I schedule this batch file to run "every night" and then backup the
backup file (if that makes sense)

weekday
IF ERRORLEVEL 0 IF NOT ERRORLEVEL 1 SET DayOfWeek=Sunday
IF ERRORLEVEL 1 IF NOT ERRORLEVEL 2 SET DayOfWeek=Monday
IF ERRORLEVEL 2 IF NOT ERRORLEVEL 3 SET DayOfWeek=Tuesday
IF ERRORLEVEL 3 IF NOT ERRORLEVEL 4 SET DayOfWeek=Wednesday
IF ERRORLEVEL 4 IF NOT ERRORLEVEL 5 SET DayOfWeek=Thursday
IF ERRORLEVEL 5 IF NOT ERRORLEVEL 6 SET DayOfWeek=Friday
IF ERRORLEVEL 6 SET DayOfWeek=Saturday
osql -E -S .\microsoftsmlbiz -Q "backup database yourBCMDBName to disk
= 'C:\Blah\Blah\%DayOfWeek% yourBCMDBName.bak' WITH INIT"

weekday is a little com file I found that determines the day of the
week (no surprise there!)

Its the osql bit that does the work.

I take NO credit for this whatsoever

Good luck

Stuart
 
R

Remove ABCD from Email address to reply

I really do not understand the batch file that you provided.

I realize that you are determining the day of the week and then doing a
backup somehow using the osql line. I do not understand that line at all,
could you explain it please.
 
A

AuditMaster

I really do not understand the batch file that you provided.

I realize that you are determining the day of the week and then doing a
backup somehow using the osql line. I do not understand that line at all,
could you explain it please.

--

Neil













- Show quoted text -

osql is a command line SQL processor. It will submit SQL to a
specified database on a specified server. I use it as I can put it in
a batch file and then use schedular to make it run every night. Bring
up a command prompt and type osql /? for more help but...

-E forms a trusted connection from the caller to the server and the -S
specifies the server we are hitting. Unless you have installed things
differently your BCM server is .\microsoftsmlbiz (note the .\ bit).
So, we have asked the sql parser to make a secure connection to the
small business server which is running your BCM database. The -Q is
the Query we want to send to the server. So, lets examine the SQL we
are sending to the server

Now, the server can support several databases so we need to tell it
the name of the BCM database we are using. This is the name of the
database you created from your initial BCM setup. We are wanting to do
a backup so our SQL query is "backup database X" I say X 'cause I dont
know what you called your BCM DB. We also need to tell it where to
send the backup to, as part of the SQL command we say "to disk=A
Filename" where A Filename is the fully qualified path to your backup
file (it is THIS file that will be backed up by your existing backup
software) Now, I happen to store my backups in c:\this\that\theother\
(I dont of course, but there you go). The stuff about day of the week
is to automatically generate a file called Monday.bak then one called
Tuesday.bak then one called Wednesday.bak etc. Now when 7 days have
gone by I start to overwrite the files and the WITH INIT bit ensures
that a clean copy is generated.

In essence, go into a command prompt and type...

osql -E -S .\microsoftsmlbiz -Q "backup database XXX to disk= 'C:\Blah
\Blah\Test.bak' WITH INIT"

be sure to use the single quotes around the file name and the double
quotes around the -Q parameter. Also, replace the XXX with the name of
your BCM database. This SHOULD create a file called Test.bak in c:\blah
\blah (you may well want to replace the blah blah as well) which can
be restored to the BCM by a SQL restore command (more later)

Hope that helps, if not feel free to ask for more and I'll email you
direct

Stuart
 
R

Remove ABCD from Email address to reply

Thanks for the complete explanation, it seems to make sense to me. I would
appreciate it if you could post or email me directly with the batch file
"weekday" and then I will put the whole thing to test.

Thanks again
 
R

Remove ABCD from Email address to reply

Stuart,
I got your email and replied to you personally but I am guessing that I was
caught in your junk email.

Thanks I got the backup to work. Of course, that resulted in a couple of
questions.

The database file is really 2 files (.ldf & .mdf) and I only have 1 backup
file (.bak). If I had to recover all of the data, how do I get the info out
of the .bak file into the database?

The second question is unrelated. When I start Windows XP Pro, the SQL
Server Service Manager starts with the window open rather than minimized. I
am sure there must be a way of starting the service in a minimized window,
do you know how?

Thanks for your help, having a backup of my database will really be helpful.
 
R

Remove ABCD from Email address to reply

Does anyone know how to restore the file from the backup created by the
batch file?
 
L

Luther

Does anyone know how to restore the file from the backup created by the
batch file?

--

Neil

in message







- Show quoted text -

You should be able to restore it using Sql Server tools like Studio,
or Enterprise Manager.

Or use osql:
osql -E -S .\microsoftsmlbiz -Q "restore database yaddayadda"

You'll need to look up the sql restore command on msdn for the actual
syntax of the yaddayadda part; it depends on the path to your backup
file and more.
 
R

Remove ABCD from Email address to reply

Thanks for the response, but I am having trouble finding the exact format of
the command. If there is anyone that knows the exact formatting of the
command, I would appreciate the help.
 
L

Lon Orenstein

Stuart:

Could you point me to the weekday file that returns the day? Couldn't find
it Googling...

Thanks much,
Lon

___________________________________________________________
Lon Orenstein
pinpointtools, llc
(e-mail address removed)
Author of Outlook 2007 Business Contact Manager For Dummies
Author of the eBook: Moving from ACT! to Business Contact Manager
800.238.0560 x6104 Toll Free (U.S. only) +1 214.905.0401 x6104
www.pinpointtools.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top