Automatic backup of a database in SQL EXPRESS 2005
On May 01,2022 by Tom Routley
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%
ame _of_backupfile_%J%_%M%_%A%_A_%H%_%MN%_%S%.bak IF NOT exist "%DIRECTORY%" md "%DIRECTORY%" cd C:Program FilesMicrosoft SQL Server90ToolsBinn sqlcmd -S Server_nameSQLEXPRESS -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.
is the location where you want to save your backup .bat file. The name _of_backupfile is the name of the .bat file
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.
Leave a Reply