|
|
|
date: Mon, 23 Jun 2008 09:58:23 -0400,
group: microsoft.public.sqlserver.msde
back
Re: backup script help!
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
date: Mon, 23 Jun 2008 16:48:58 +0200
author: Andrea Montanari
Re: backup script help!
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
"Andrea Montanari" wrote:
> 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
>
>
>
date: Wed, 23 Jul 2008 12:11:09 -0700
author: Todd C
|
|
