Post reply

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

  • August 3, 2011 at 7:17 am

    #242546

    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.

  • August 3, 2011 at 7:21 am

    #1363024

    http://standalonesqlagent.codeplex.com/

  • August 3, 2011 at 7:27 am

    #1363030

    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

  • August 3, 2011 at 7:32 am

    #1363036

    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:.

  • August 3, 2011 at 7:49 am

    #1363051

    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

  • August 3, 2011 at 7:53 am

    #1363056

    Thanks for the link http://standalonesqlagent.codeplex.com/ but need something that's not in Beta.

  • August 3, 2011 at 8:04 am

    #1363061

    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.

  • August 3, 2011 at 8:06 am

    #1363065

    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

  • August 3, 2011 at 8:08 am

    #1363067

    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

  • August 3, 2011 at 9:13 am

    #1363140

    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.

  • August 3, 2011 at 9:17 am

    #1363144

    Thanks for the feedback, adding to briefcase.

  • January 17, 2013 at 8:13 am

    #1578331

    Try SQL Backup Master. It can do the job, and is free:

    http://www.sqlbackupmaster.com

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply