Database Backup using Batch File

Backing up databases ensures that data remains safe in the event of accidental deletions, hardware failures, or software issues. While there are many tools and methods available for database backup, one straightforward and highly customizable method involves using a batch file.

In this post, we'll walk through how to create a batch file to back up a database specifically for SQL Server and MySQL databases and PostgreSQL.

Database Backup using Batch File

Getting Started

Backup database sql server or MySQL is one of the most critical tasks in IT operations but database back up also required to secure data of database.

There are many GUI based tools are available in market to take backup of the data of database, those are might be expensive also, using a batch file remains a simple, automated, and flexible solution.

This approach is helpful for users without knowledge of SSMS, provided they have the server name, user ID, and password.

Batch File For Backup SQL Database

Here's a basic batch file to back up a SQL Server database using sqlcmd and sqlbackup via sqlcmd:

Batch Programming Script

 @echo off  
 :: Set variables  
 set SERVER_NAME=localhost  
 set DB_NAME=MyDatabase  
 set BACKUP_PATH=C:\DatabaseBackups  
 set DATESTAMP=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%  
 :: Create backup folder if not exists  
 if not exist "%BACKUP_PATH%" (  
   mkdir "%BACKUP_PATH%"  
 )  
 :: SQL Server backup command  
 sqlcmd -S %SERVER_NAME% -E -Q "BACKUP DATABASE [%DB_NAME%] TO DISK = N'%BACKUP_PATH%\%DB_NAME%_%DATESTAMP%.bak' WITH INIT"  
 echo SQL Server backup completed for %DB_NAME% on %DATESTAMP%  
 pause  

Batch File For MySql Backup

Here's a simple batch file to automate MySQL database backups on a Windows machine.

Batch Programming Script

 @echo off  
 :: Set variables  
 set DB_USER=root  
 set DB_PASSWORD=yourpassword  
 set DB_NAME=mydatabase  
 set BACKUP_PATH=C:\DatabaseBackups  
 set DATESTAMP=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%  
 :: Create backup folder if it doesn't exist  
 if not exist "%BACKUP_PATH%" (  
   mkdir "%BACKUP_PATH%"  
 )  
 :: Backup command  
 mysqldump -u %DB_USER% -p%DB_PASSWORD% %DB_NAME% > "%BACKUP_PATH%\%DB_NAME%_%DATESTAMP%.sql"  
 :: Optional: Zip the backup  
 :: powershell Compress-Archive -Path "%BACKUP_PATH%\%DB_NAME%_%DATESTAMP%.sql" -DestinationPath "%BACKUP_PATH%\%DB_NAME%_%DATESTAMP%.zip"  
 :: del "%BACKUP_PATH%\%DB_NAME%_%DATESTAMP%.sql"  
 echo Backup completed on %DATESTAMP%  
 pause  

Batch File For Postgres Backup

PostgreSQL backups are done using pg_dump. Here’s a batch file for automating it:

Batch Programming Script

 @echo off  
 :: Set variables  
 set PG_PATH="C:\Program Files\PostgreSQL\15\bin"  
 set DB_NAME=mydatabase  
 set DB_USER=postgres  
 set BACKUP_PATH=C:\DatabaseBackups  
 set DATESTAMP=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%  
 :: Create backup folder if it doesn't exist  
 if not exist "%BACKUP_PATH%" (  
   mkdir "%BACKUP_PATH%"  
 )  
 :: Set path to include PostgreSQL bin directory  
 set PATH=%PG_PATH%;%PATH%  
 :: Backup command  
 pg_dump -U %DB_USER% -F c -b -v -f "%BACKUP_PATH%\%DB_NAME%_%DATESTAMP%.backup" %DB_NAME%  
 echo PostgreSQL backup completed for %DB_NAME% on %DATESTAMP%  
 pause  

Notes:

• You may be prompted for the password. To avoid this, set the PGPASSWORD environment variable:

  set PGPASSWORD=yourpassword

• %PG_PATH% must be set to the correct bin directory for your PostgreSQL installation.

Batch File Command

-S, -U, -P and -d are command line options used with SQLCMD batch command to execute sql queryies. See the below details for more information.

1. -S Specifies the instance of SQL Server to which to connect. It sets the sqlcmd scripting variable SQLCMDSERVER.
2. -d Issues a USE db_name statement when you start sqlcmd.
3. -U Is the login name or contained database user name. For contained database users, you must provide the database name option (-d).
4. -P Is a user-specified password. Passwords are case-sensitive.
5. -i Identifies the file that contains a batch of SQL statements or stored procedures.
6. -o Identifies the file that receives output from sqlcmd.
7. -v Creates a sqlcmdscripting variable that can be used in a sqlcmd script.

Summary

Using a batch file for database backups is an effective way to automate and simplify the process, especially in Windows environments. While it may seem old-fashioned, it remains reliable, customizable, and easy to schedule. I hope this was helpful to you.

Thanks