Scheduled SQL 2005 Express backup

Posted on 2007-10-08
Last Modified: 2008-01-09
I am looking to set up a scheduled backup for MSSQL Express. I have tried a few methods - some success running a bat file with:-

osql -U sa -P mypassword -n -Q "Backup database dbname to disk = 'c:\dbname.bak'"

But cannot get this to work on current database.

Have seen reference to sqlcmd in current logs but cannot see a clear way of doing this.

also method using - EXECUTE master.dbo.xp_sqlmaint but I am not sure where or how to run this as a scheduled backup.

Appreciate it if someone could give me some help.
Question by:donhannam
    LVL 25

    Expert Comment

    LVL 5

    Expert Comment

    As SQL Express does not contain the same agent features as the other editions you need to create a batch script to execute the backup command using osql or sqlcmd and then schedule the batch script using the Scheduled Tasks icon in the Control Panel.

    Author Comment


    Thanks for this - assume you mean the master.dbo.xp_sqlmaint command will not work.

    I have tried options with osql and sqlcmd but cannot get it to work.

    Used the following in looking at jogos comment.

    sqlcmd -S"don\sqlexpress" -E -Q"BACKUP DATABASE Test TO DISK = 'C:\Test.Bak' WITH INIT'"

    I saved this to desktop and double clicked to see if it would run - dos window flicked up but no bak file created.

    Also run from scheduled tasks and tried with sa password rather than -E

    I have MSSQL Express and MSDE on PC am testing on.

    LVL 25

    Accepted Solution


    Try first to find out if the BACKUP-command your using is working fine in a query window, witch error it's giving ...

    Secondly try to do it as a bachtjob.

    Author Comment


    Thanks for this running in query window identified did not have permission to back up in this file. Changed to:-

    sqlcmd -S"don\sqlexpress" -E -Q"Backup database Test to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test.bak' WITH INIT"

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now