Automatic backup of a database in SQL EXPRESS 2005

October 2016

SQL is one of the most widely used databases across the world. Structured Query Library or SQL as it is commonly known, is the programming language used to manage data on a SQL database. SQL Express 2005 is a version of SQL server which gives users a lot of flexibility in designing and automating their SQL scripts. One can create automatic backup of data in SQL Express 2005 by using a .bat file. One has to provide the directory name where the backup needs to be, along with time and date information in a text file; save the file with a .bat extension.


If you want to automate a backup of a database in SQL Server Express, there is a simple solution using a .bat. file.


Create a new text file. This file should contain the following information:

SET J=%date:~-10,2%     
SET A=%date:~-4%     
SET M=%date:~-7,2%     
SET H=%time:~0,2%     
SET MN=%time:~3,2%     
SET S=%time:~-5,2%     

IF "%time:~0,1%"==" " SET H=0%HOURS:~1,1%     

SET DIRECTORY=E:\Directory_name\     

FILE=%DIRECTORY%\name _of_backupfile_%J%_%M%_%A%_A_%H%_%MN%_%S%.bak     

IF NOT exist "%DIRECTORY%" md "%DIRECTORY%"     

cd C:\Program Files\Microsoft SQL Server\90\Tools\Binn     

sqlcmd -S Server_name\SQLEXPRESS -Q "BACKUP DATABASE Name_of_sql_database TO DISK = N'%FILE%' WITH INIT, NAME = N'Automatic back up of database', STATS = 1"

Finally, change the extension. Txt. Bat


  • The E:\Directory_name\ is the location where you want to save your backup .bat file.
  • The name _of_backupfile is the name of the .bat file
  • Finally, the Name_of_sql_database is the name of the database.

In addition, it displays the date and time of the backup name assigned to the .bat file.

Then just create a scheduled task.

Related :

This document entitled « Automatic backup of a database in SQL EXPRESS 2005 » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.