Scheduled Backup for SQL Server Express

17 Feb 2006 3:50 PM

BCS

Not sure if this is the right forum, so please correct me if I'm in error.
I've written a Timeclock application in VB6 for our small chain of retail
stores and storing the data in a SQL Server Express database. All is
working well, but I want to perform a nighly backup of the database and
there appears to be no Maintenance Plan setup wizard in SQL Server Express.

Can anyone provide me with a query that I can launch at a scheduled time to
perform this or another alternative.

Thank you,

Barry

17 Feb 2006 4:10 PM

Evergray

There is no SQL Server Agent in Express edition of SQL Server 2005, so no
scheduled tasks can be performed using standard tools. You may use OS
scheduler and SQLCMD to do this job or write your own backup app. Or, if
your Express instances can be accessed as linked servers from Standard or
Enterprise instance, you may do so by scheduling remote procedure calls on
this instance.

--
WBR, Evergray
--
Words mean nothing...

Show quote

"BCS" <bswed***@tayloroil.com> wrote in message
news:NgmJf.21119$no3.16791@tornado.southeast.rr.com...
> Not sure if this is the right forum, so please correct me if I'm in error.
> I've written a Timeclock application in VB6 for our small chain of retail
> stores and storing the data in a SQL Server Express database. All is
> working well, but I want to perform a nighly backup of the database and
> there appears to be no Maintenance Plan setup wizard in SQL Server
> Express.
>
> Can anyone provide me with a query that I can launch at a scheduled time
> to
> perform this or another alternative.
>
> Thank you,
>
> Barry
>
>

17 Feb 2006 4:13 PM

Andrew J. Kelly

The command to do a backup is BACKUP and you can see all the details in
BooksOnLine. But which options you use and the exact syntax depends on
exactly what you want to do and how. You might want to have a look at BOL
under BACKUP to get an idea what you want first to see what is available.

--
Andrew J. Kelly SQL MVP

Show quote

"BCS" <bswed***@tayloroil.com> wrote in message
news:NgmJf.21119$no3.16791@tornado.southeast.rr.com...
> Not sure if this is the right forum, so please correct me if I'm in error.
> I've written a Timeclock application in VB6 for our small chain of retail
> stores and storing the data in a SQL Server Express database. All is
> working well, but I want to perform a nighly backup of the database and
> there appears to be no Maintenance Plan setup wizard in SQL Server
> Express.
>
> Can anyone provide me with a query that I can launch at a scheduled time
> to
> perform this or another alternative.
>
> Thank you,
>
> Barry
>
>

17 Feb 2006 4:30 PM

Mark Williams

You could use a combination of SQL statements and Scheduled Tasks:

CREATE PROCEDURE nightlybackup
AS

BACKUP DATABASE [yourdb]
TO DISK = 'c:\backups\dbbackup.dat'

BACKUP LOG [yourdb]
TO DISK = 'c:\backups\dblogbackup.dat'

Now create a cmd script; run the below at a command prompt (all on one line,
of course):

echo osql -d [yourdb] -E -Q "EXEC
dbo.nightlybackup">>c:\backups\nightlybackup.cmd

Now add it as a scheduled task (all on one line again):

schtasks /create /tn nightlybackup /tr c:\backups\nightlybackup.cmd /sc
daily /st 00:05:00

This will do a daily backup at 5 minutes after midnight. Do all of this
logged on as a local administrator.

--

Show quote

"BCS" wrote:

> Not sure if this is the right forum, so please correct me if I'm in error.
> I've written a Timeclock application in VB6 for our small chain of retail
> stores and storing the data in a SQL Server Express database. All is
> working well, but I want to perform a nighly backup of the database and
> there appears to be no Maintenance Plan setup wizard in SQL Server Express.
>
> Can anyone provide me with a query that I can launch at a scheduled time to
> perform this or another alternative.
>
> Thank you,
>
> Barry
>
>
>

18 Feb 2006 8:01 PM

BCS

Thanks to everyone for your input.

Barry

Show quote

"Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message
news:BD7B78CE-DB95-4E1D-89FC-41F689E94725@microsoft.com...
> You could use a combination of SQL statements and Scheduled Tasks:
>
> CREATE PROCEDURE nightlybackup
> AS
>
> BACKUP DATABASE [yourdb]
> TO DISK = 'c:\backups\dbbackup.dat'
>
> BACKUP LOG [yourdb]
> TO DISK = 'c:\backups\dblogbackup.dat'
>
> Now create a cmd script; run the below at a command prompt (all on one
line,
> of course):
>
> echo osql -d [yourdb] -E -Q "EXEC
> dbo.nightlybackup">>c:\backups\nightlybackup.cmd
>
> Now add it as a scheduled task (all on one line again):
>
> schtasks /create /tn nightlybackup /tr c:\backups\nightlybackup.cmd /sc
> daily /st 00:05:00
>
> This will do a daily backup at 5 minutes after midnight. Do all of this
> logged on as a local administrator.
>
> --
>
> "BCS" wrote:
>
> > Not sure if this is the right forum, so please correct me if I'm in
error.
> > I've written a Timeclock application in VB6 for our small chain of
retail
> > stores and storing the data in a SQL Server Express database. All is
> > working well, but I want to perform a nighly backup of the database and
> > there appears to be no Maintenance Plan setup wizard in SQL Server
Express.
> >
> > Can anyone provide me with a query that I can launch at a scheduled time
to
> > perform this or another alternative.
> >
> > Thank you,
> >
> > Barry
> >
> >
> >