SQL Server Express is a free edition and is available with a number of restrictions. For example; Your database size should not exceed 10GB and there are restrictions, such as SQL Server Agent is disabled, Auto tasks are disabled.
So you are running SQL Server (2005 or 2008) Express (presumably installed as part of an application server install) and you would like to make sure your databases are backing up each night. You go to load the SQL Server Management Studio only to discover it’s not there. So then you head on over to Microsoft’s website and download the install for. But then you soon discover there are no maintenance plan options, and the SQL Server Agent is nonexistent. So how do you backup a SQL Express database?Here’s what Microsoft has to say about the situation:SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. Therefore, you have to take a different approach to back up your databases when you use these editions.
Luckily for us they proceed to offer us that “different approach” they mention. In the Microsoft Knowledge Base article (, ““), they offer us a stored procedure and several batch file scenarios. Be sure to check out that article to gain a full understanding of all the options. I will offer one of those options here, along with that stored procedure, and then I will also add in a line of batch file code that will allow you to delete older backups, giving you a true replacement for the SQL Agent / maintenance plan method of SQL database backups.STEP 1Run the below script on your SQL Express server (I assume at this point you have the SQL Server Management Studio installed).
By: Updated: 2018-06-02 Related: ProblemAs a lot of line-of-business applications are being built with SQL Server ExpressEdition as their backend database, we need to make sure that we backup the systemand the user databases running on these instances. Unfortunately, SQL Server ExpressEdition does not come with SQL Agent which we would normally use to create a databasemaintenance plan to backup all the databases. How do we perform a backup of oursystem and user databases in SQL Server Express Edition similar to how we do itin other editions? SolutionWe can use a combination of VBScript and TSQL with Task Manager in Windows toautomate the creation of user and system database backups in SQL Server ExpressEdition.Note: All files should be saved in folder E:SQLBackupscripts.This can be changed, but this example is setup for this folder. If you saveto a different folder you will need to update the scripts accordingly. Step 1 - Create the TSQL scriptThe TSQL script below generates a database backup similar to the formatting generatedby the database maintenance plan, taking into account the date and time the backupfiles were generated. We save the script as a.sql file, E:SQLBackupscripts backupDB.sql,which we will call from a batch file using sqlcmd.
On Error Resume NextDim fso, folder, files, sFolder, sFolderTargetSet fso = CreateObject ( 'Scripting.FileSystemObject' )'location of the database backup filessFolder = 'E:SQLBackup'Set folder = fso.GetFolder ( sFolder )Set files = folder.Files'used for writing to textfile - generate report on database backups deletedConst ForAppending = 8'you need to create a folder named 'scripts' for ease of file management &'a file inside it named 'LOG.txt' for delete activity loggingSet objFile = fso.OpenTextFile ( sFolder & 'scriptsLOG.txt', ForAppending )objFile. Write ' & VBCRLF & VBCRLFobjFile.
Write ' DATABASE BACKUP FILE REPORT ' & VBCRLFobjFile. Write ' DATE: ' & FormatDateTime ( Now , 1 ) & ' & VBCRLFobjFile. Write ' TIME: ' & FormatDateTime ( Now , 3 ) & ' & VBCRLF & VBCRLFobjFile. Write ' & VBCRLF'iterate thru each of the files in the database backup folderFor Each itemFiles In files'retrieve complete path of file for the DeleteFile method and to extract'file extension using the GetExtensionName methoda = sFolder & itemFiles. Name'retrieve file extensionb = fso.GetExtensionName ( a )'check if the file extension is BAKIf uCase ( b ) = 'BAK' Then'check if the database backups are older than 3 daysIf DateDiff ( 'd', itemFiles.DateCreated, Now ) = 3 Then'Delete any old BACKUP files to cleanup folderfso.DeleteFile aobjFile.WriteLine 'BACKUP FILE DELETED: ' & aEnd IfEnd IfNextobjFile.WriteLine ' & VBCRLF & VBCRLFobjFile. CloseSet objFile = NothingSet fso = NothingSet folder = NothingSet files = NothingStep 3 - Create the batch file that will call the TSQL script and the VBScriptfileWe need to create the batch file which will call both the TSQL script and theVBScript file. The contents of the batch file will be a simple call to the sqlcmd.exeand a call to the VBScript file using either wscript.exe or simply calling the file.Save the file as E:SQLBackupscripts databaseBackup.cmd and save it inthe scripts subfolder.
REM Run TSQL Script to backup databasessqlcmd -S-E -i'E:SQLBackupscriptsbackupDB.sql'REM Run database backup cleanup scriptE:SQLBackupscriptsdeleteBAK.vbs Step 4 - Create a task in Windows Task SchedulerCreate a daily task in Windows Task Scheduler that will call the batch file createdin the previous step. This can be found in the Control Panel Scheduled Tasksor under Start All Programs Accessories System Tools ScheduledTasks.Since we are using Windows authentication to run the TSQL script, use a Windowsaccount that is a member of the dbbackupoperator role of all the databases. Launch 'Scheduled Tasks'. Click on Add Scheduled Task. Browse to the 'E:SQLBackupscripts' folder and select databaseBackup.cmd. Pick the frequency and time for the backups to run.
Lastly, enter a Windows account that has at least dbbackupoperator roleprivileges for all of the databases. See screenshots below. Post a comment or let the author know this tip helped.All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (.).Name.EmailEmail me updates.
NOTE. If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.Signup for our newsletterI agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the and understand I may unsubscribe at any time. Thursday, April 14, 2016 - 12:43:48 PM - bassplayerHi Salmndr,This works with SQL Server 2000. Just replace SQLCMD with OSQL.