Scheduled SQL 2005 Express backup

Posted on 2007-10-08
Medium Priority
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
  • 2
  • 2
LVL 25

Expert Comment

ID: 20038116

Expert Comment

ID: 20039580
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

ID: 20043711

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

jogos earned 1500 total points
ID: 20046700

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

ID: 20046877

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

840 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