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:
IF "%time:~0,1%"==" " SET H=0%HOURS:~1,1%
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.