<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
SQL 2008R2 Express | SQL Agent help...Need to...
12 posts, Page 1 of 2
1
2
»»
SQL 2008R2 Express | SQL Agent help...Need to set up a backup scheduled task.
Rate Topic
Display Mode
Topic Options
Author
Message
AHWB
AHWB
Posted Wednesday, August 03, 2011 7:17 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, February 04, 2013 3:09 AM
Points: 35,
Visits: 80
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.
Post #1153499
Ninja's_RGR'us
Ninja's_RGR'us
Posted Wednesday, August 03, 2011 7:21 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 21,357,
Visits: 9,539
http://standalonesqlagent.codeplex.com/
Post #1153505
GilaMonster
GilaMonster
Posted Wednesday, August 03, 2011 7:27 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 37,739,
Visits: 30,013
You don't need any software at all. Use the windows scheduler.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1153511
Ninja's_RGR'us
Ninja's_RGR'us
Posted Wednesday, August 03, 2011 7:32 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 21,357,
Visits: 9,539
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
.
Post #1153522
AHWB
AHWB
Posted Wednesday, August 03, 2011 7:49 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, February 04, 2013 3:09 AM
Points: 35,
Visits: 80
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
Post #1153539
AHWB
AHWB
Posted Wednesday, August 03, 2011 7:53 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, February 04, 2013 3:09 AM
Points: 35,
Visits: 80
Thanks for the link http://standalonesqlagent.codeplex.com/ but need something that's not in Beta.
Post #1153544
Ninja's_RGR'us
Ninja's_RGR'us
Posted Wednesday, August 03, 2011 8:04 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 21,357,
Visits: 9,539
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.
Post #1153549
GilaMonster
GilaMonster
Posted Wednesday, August 03, 2011 8:06 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 37,739,
Visits: 30,013
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 2008, MVP
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
Post #1153553
GilaMonster
GilaMonster
Posted Wednesday, August 03, 2011 8:08 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 37,739,
Visits: 30,013
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
.
I don't, but I know that a google search will turn some up.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1153555
AHWB
AHWB
Posted Wednesday, August 03, 2011 9:13 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, February 04, 2013 3:09 AM
Points: 35,
Visits: 80
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.
Post #1153636
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
