Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008R2 Express | SQL Agent help...Need to set up a backup scheduled task.


SQL 2008R2 Express | SQL Agent help...Need to set up a backup scheduled task.

Author
Message
AHWB
AHWB
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 91
This is my first look at SQL Express 2008r2 and I have realised that SQL agent isn't part of the express edition. Normally on 2005/2008 std/ent I use SQL agent to schedule database backups and backup the transaction log etc.

My first question is;

1) How do you set up a scheduled task to back up a database, initial search suggests you have to use windows task scheduler but how is this done, or is there a better way?

2) Following on from question 1, is there good, preferably free, software which can do what MS SQL agent does. I know MSDE Vale have their own SQL agent, is this any good or are there other options.

3) linked with questions 1 and 2, I assume SQLexpress still requires the transaction log to be backed up, should this be set up using as per question 1 & 2 above, I'm thinking of setting the recovery method to simple as the system that this is used on doesn't really require Full.

I have searched the web but couldn't find much on these specific questions.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20927 Visits: 9671
http://standalonesqlagent.codeplex.com/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
You don't need any software at all. Use the windows scheduler.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20927 Visits: 9671
GilaMonster (8/3/2011)
You don't need any software at all. Use the windows scheduler.


Go sample backup code for that option? Never tried to make it work that way Ermm.
AHWB
AHWB
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 91
I understand that you can set up a backup task using windows scheduler, but how, I need help with the syntax.

Looking at windows scheduler, Windows 7

click create task (all the other options within windows scheduler are self explanatory)
on the 'actions' tab click 'new'
there is a 'program/script' files (with a browse button next to it)
I entered the following - path to sqlcmd

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE"

and then within the add argument (optional) field the following

backup database database_name to disk = 'c:\database_name.bak'

this did not work, i expect i need to add a login or something....so can anyone help with the syntax required as im not familiar with sqlcmd
AHWB
AHWB
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 91
Thanks for the link http://standalonesqlagent.codeplex.com/ but need something that's not in Beta.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20927 Visits: 9671
AHWB (8/3/2011)
Thanks for the link http://standalonesqlagent.codeplex.com/ but need something that's not in Beta.


ok, nothing else to offer beying what Gail already provided.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
AHWB (8/3/2011)
so can anyone help with the syntax required as im not familiar with sqlcmd


SQLCMD /? prints out the command-line options (like with most similar programs) or http://msdn.microsoft.com/en-us/library/ms162773.aspx


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
Ninja's_RGR'us (8/3/2011)
GilaMonster (8/3/2011)
You don't need any software at all. Use the windows scheduler.


Go sample backup code for that option? Never tried to make it work that way Ermm.


I don't, but I know that a google search will turn some up.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


AHWB
AHWB
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 91
Sorted and for all of you that are interested....

Set up a simple windows scheduled task that executes a batch file. Within the batch file I have the following

rem @echo off
sqlcmd -S localhost -d dbname -i backup.sql -U myusername -P mypassword


This then calls 'backup.sql'

which contains the following syntax

USE [dbname]
GO

DECLARE   @return_value int

EXEC   @return_value = [dbo].[prc_db-backup]
      @db_name = N'dbname',
      @db_datafile = N'datafilename',
      @db_backup_path = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup'

SELECT   'Return Value' = @return_value

GO

Which executes a stored procedure 'prc_db-backup' which contains my T-sql to create my backups

Simple.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search