Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2008R2 Express | SQL Agent help...Need to set up a backup scheduled task. Expand / Collapse
Author
Message
Posted Wednesday, August 3, 2011 7:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:34 AM
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.
Post #1153499
Posted Wednesday, August 3, 2011 7:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 10:44 AM
Points: 21,405, Visits: 9,617
http://standalonesqlagent.codeplex.com/
Post #1153505
Posted Wednesday, August 3, 2011 7:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 42,953, Visits: 36,110
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
Posted Wednesday, August 3, 2011 7:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 10:44 AM
Points: 21,405, Visits: 9,617
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
Posted Wednesday, August 3, 2011 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:34 AM
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





Post #1153539
Posted Wednesday, August 3, 2011 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:34 AM
Points: 36, Visits: 91
Thanks for the link http://standalonesqlagent.codeplex.com/ but need something that's not in Beta.
Post #1153544
Posted Wednesday, August 3, 2011 8:04 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 10:44 AM
Points: 21,405, Visits: 9,617
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
Posted Wednesday, August 3, 2011 8:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 42,953, Visits: 36,110
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
Posted Wednesday, August 3, 2011 8:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 42,953, Visits: 36,110
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
Posted Wednesday, August 3, 2011 9:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:34 AM
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.
Post #1153636
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse