Database Discussion - Scheduling database backups in MS SQL 2005 Express

Print Page | Close Window

Scheduling database backups in MS SQL 2005 Express

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=26442
Printed Date: 28 March 2026 at 4:39pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Scheduling database backups in MS SQL 2005 Express

Posted By: iSec
Subject: Scheduling database backups in MS SQL 2005 Express
Date Posted: 27 October 2008 at 6:47am

This used to be an easy task when I had SQL 2000 as the SQL 2000 Agent was part of SQL 2000 database server. However after switching to SQL 2005 Express I can no longer schedule backups... primarily because of the fact that the "express" edition of MSSQL does not have it or allow it.

I have already googled this but still I am unsure of the quickest and proper way of scheduling backups through MSSQL Express. Does anyone here know how this can be done?

-------------
"When it gets dark enough, you can see the stars"
-Charles A. Beard

Replies:

Posted By: michael
Date Posted: 06 November 2008 at 1:11am

Create a SP that does all the backups you need - schedule a Windows Job to run every day or so.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: WebWiz-Bruce
Date Posted: 06 November 2008 at 7:56am

I use to do SQL Server backups through the command line for this website for SQL Server 2005 Express by creating a batch file called backupSqlServer.bat.

The code was:-

REM *****************************
REM Backup SQL Server
REM *****************************

REM To restore use SQL Manager Studio - Restore form file using .bak file

md c:\Backup\Database


REM Backup Web Wiz SQL Server DB's
REM *********
osql -E -S .\ -Q "BACKUP DATABASE Web Wiz TO DISK='c:\Backup\Database\Web Wiz2.bak'"
REM SQL Server appends to previous backup making larger files, so save Web Wiz2.bak then delete and rename Web Wiz.bak
if exist c:\Backup\Database\Web Wiz2.bak goto exist
goto end
:exist
del c:\Backup\Database\Web Wiz.bak
ren c:\Backup\Database\Web Wiz2.bak Web Wiz.bak
:end

You then create a scheduled tasks in windows to run the backupSqlServer.bat file whenever you want.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: MadDog
Date Posted: 06 November 2008 at 6:32pm

Thank you Bruce! Just what i was looking for Thumbs Up

-------------
http://www.iportalx.net" rel="nofollow">

Posted By: ntk
Date Posted: 02 March 2009 at 2:36pm

I had to do this recently. My background is network and Win sys admin with limited dba skill.

What I found helpful was to install SQL Server 2005 Management Studio Express.

Once you've installed Mgmt Studio, pick the DB in question, right-click on the DB, choose All Tasks > Backup. Pick the right options for your case, and toward the middle on top is a button that says Script. Choose Script to File. Save to file and on the Backup window choose cancel (if you hit OK it'll run a backup, which may not be what you want at that time). This process will create a FileName.sql file for you.

Create a .bat file which for me looks like:

@ECHO off
ECHO Attempting to backup Database
sqlcmd -S localhost -E /i FileName.sql
ECHO.
ECHO If no error message is shown, then the database was backed up correctly.
ECHO.
PAUSE

Note: I placed the .bat and .sql in the same directory, hence the argument to /i is not absolute path.

Run the .bat file, and if there are no errors modify the batch file by removing the PAUSE.

Create a scheduled task to run the .bat file.