Automatic backup of a database in SQL EXPRESS 2005

January 2017

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.


Intro


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

Implementation


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

Notes

  • 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


Published by jak58. Latest update on March 10, 2012 at 09:15 AM by Virginia Parsons.
This document, titled "Automatic backup of a database in SQL EXPRESS 2005," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).