backup script help!

[James Landon]

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

[Andrea Montanari]

hi,

Post by James Landon
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

[Todd C]

James:
To add to Andrea's insightful reply, you can also use the SQLCmd command
line utility to execute a script, and/or output the results to a file.

Check Books On Line for more info on SQLCmd, namely the -S(Server), -i
(input file)and -o (output file) switches.

Now, with your scheduling tool of choice, you could execute something like
sqlcmd -S <MachineName>\SQLEXPRESS -i <file to execut> -o <file to capture
results>

By swapping out your input file, you can perform maintenance on the
databases and other tasks without needing to re-program the scheduler.

Do yourself a favor and dive into learining T-SQL. If you are going to be
supporting remote SQL installations, it will come in handy.
--
Todd C

Post by Andrea Montanari
hi,

Post by James Landon
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