backup script help!

Hi,

I am having a very difficult time trying to figure out how to write a script
for a SQL backup. I will try and explain what I am trying to do.

The majority of our computers out at customer sites are running access
databases, however a few other computers are running on a SQL Database.
Currently we are backing up the Access DB's by connecting through a VPN and
copying the "live data" directly from the customers end to the local
computer. Then we transfer the MDB and TAG file back to our end via a file
transfer option on the VPN that we use. That has been working for us so far
with the 15 computers we have out there. Obviously, the issue with this is
it is not efficient at all and as we grow the number of computers we have
out there it will take more and more time to complete. Plus we need to
eventually upgrade everything to the SQL DB.

We are using MSDE 2005 instead if the Full SQL Server and through the
newsgroups I have learned that there is no job scheduler built in to the
express edition to set an auto backup. I need to find a way to create a
auto-backup for the sql database on the customers computer before we get
into the office on the morning.

Unfortunately, I do not very much experience at all with writing code. I can
provide all the needed details as far as server name, log-on, passwords
etc....however I cannot write the backup script needed. Is it possible to
for someone to either help me write this code or direct me to a website that
might provide a lot more detail on how to do this? I will appreciate any
info you can give me on this matter. Thanks in advance for your help.

Sincerely James

hi,
James Landon wrote:
> Hi,
>
> I am having a very difficult time trying to figure out how to write a
> script for a SQL backup. I will try and explain what I am trying to
> do.
> The majority of our computers out at customer sites are running access
> databases, however a few other computers are running on a SQL
> Database. Currently we are backing up the Access DB's by connecting
> through a VPN and copying the "live data" directly from the customers
> end to the local computer. Then we transfer the MDB and TAG file back
> to our end via a file transfer option on the VPN that we use. That
> has been working for us so far with the 15 computers we have out
> there. Obviously, the issue with this is it is not efficient at all
> and as we grow the number of computers we have out there it will take
> more and more time to complete. Plus we need to eventually upgrade
> everything to the SQL DB.
> We are using MSDE 2005 instead if the Full SQL Server and through the
> newsgroups I have learned that there is no job scheduler built in to
> the express edition to set an auto backup. I need to find a way to
> create a auto-backup for the sql database on the customers computer
> before we get into the office on the morning.
>
> Unfortunately, I do not very much experience at all with writing
> code. I can provide all the needed details as far as server name,
> log-on, passwords etc....however I cannot write the backup script
> needed. Is it possible to for someone to either help me write this
> code or direct me to a website that might provide a lot more detail
> on how to do this? I will appreciate any info you can give me on this
> matter. Thanks in advance for your help.
> Sincerely James

for SQLExpress you have to rely on alternate scheduler as, as you already
pointed out, the SQL Agent component is not available for this sku..
usually you can use the native OS scheduler, where you define a task to
execute a .cmd file which include a call to SQLCmd.exe, the command line
tool provided along with SQL Server\SQLExpress, [
http://msdn.microsoft.com/en-us/library/ms162773.aspx ] with a query
statement that performs the required backup, thus a
BACKUP [dbname] TO DISK = 'full_path';
this scenario is "gratis" and is well supported and even explained in good
articles as http://www.sqldbatips.com/showarticle.asp?ID=27 and
http://www.sqldbatips.com/showarticle.asp?ID=29..
or you can rely on an alternate scheduler like
http://www.valesoftware.com/products-express-agent.php (commercial) or
http://www.codeproject.com/KB/database/SQLAgent.aspx (free)..
a third solution is based on the SQLExpress limited support of the Service
Broker, ase described in
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx ..
regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://www.hotelsole.com
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply