Scripting: Backing Up SQL Server Express Databases

Microsoft SQL Server Express editions do not include SQL Agent so backups cannot be scheduled within SQL Server Management Studio. However the backup can be automated using batch scripts and the Task Scheduler.

1. Use Notepad to create a .BAT file containing the following:

   @echo off
   
   rem For the following line, substitute the location of SQLCMD.EXE on your system
   
   cd /d "C:\Program Files\Microsoft SQL Server\100\Tools\Binn"
   
   sqlcmd.exe -S SERVER\Instance -E -Q "BACKUP DATABASE DatabaseName TO DISK = 'C:\Backups\Database.bak' WITH INIT"

   Where:

   SERVER\Instance — the server name and SQL Server instance (use syntax .\Instance if the server is local. Append “,n” for a non-standard port n. See here for more syntax);

   DatabaseName — The name of the database being backed up (if the database name contains hyphens, enclose it in []);

   WITH INIT — Causes the backup to overwrite the existing file (leave out if append is required).

   NB the command-line options for SQLCMD such as -S, -E etc must be upper case.

2. Run the batch script using Task Scheduler. Ensure Run with highest privileges is checked.

   The destination location must be writeable by SQL Server's service account.

When scripting backups it is advisable to also run a Transaction Log backup with the command BACKUP LOG for any databases not using a “Simple” recovery model. Although the BACKUP DATABASE command backs up the entire database, including the contents of the log, the log cannot be shrunk until it has been backed up. If the log does not routinely shrink, it will grow endlessly. The transaction log backup could be output to device 'NUL' if an actual backup file is not required.



Copyright © 1996-2023 Cadzow TECH Pty. Ltd. All rights reserved.
Information and prices contained in this website may change without notice. Terms of use.
Question/comment about this page? Please email webguru@cadzow.com.au