Batch Script to Take Backup of All MSSQL Databases

Introduction

Taking regular database backups is an important task for server administrators. In Windows servers running Microsoft SQL Server, we can use a batch script with the sqlcmd utility to back up all user-created MSSQL databases.

This script helps automate the backup process by listing all databases except system databases and creating .bak files in a date-wise backup folder.

---

Prerequisites

Before running the script, make sure the following requirements are met:

• Windows server with Microsoft SQL Server installed.
• sqlcmd utility should be installed and accessible from Command Prompt.
• SQL Server instance name should be known, for example:

.\SQLEXPRESS

• A valid SQL user account with backup permission should be available.
• Backup drive or folder should have enough free disk space.
• The backup destination folder should exist or be created by the script.
• Avoid using the sa account unless required. It is recommended to use a dedicated SQL backup user with limited permissions.

---

Batch Script

Open Notepad and paste the below script.

@ECHO OFF
SETLOCAL

REM MSSQL Database Backup Script
REM This script will backup all user-created databases

REM Set date format
for /F "tokens=2-4 delims=/ " %%A in ('Date /T') DO SET NowDate=%%A-%%B-%%C

REM SQL Server details
SET SQL_INSTANCE=.\SQLEXPRESS
SET SQL_USER=sa
SET SQL_PASS=YourPasswordHere

REM Backup location
SET BACKUP_PATH=D:\Backup
SET DBList=%SystemDrive%\SQLDBList.txt

ECHO Backup Date: %NowDate%

REM Create backup directory
IF NOT EXIST "%BACKUP_PATH%\%NowDate%" mkdir "%BACKUP_PATH%\%NowDate%"

REM Get list of user databases
sqlcmd -S "%SQL_INSTANCE%" -U "%SQL_USER%" -P "%SQL_PASS%" -h -1 -W -Q "SET NOCOUNT ON; SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

REM Backup each database
FOR /F "usebackq tokens=* delims=" %%I IN ("%DBList%") DO (
    IF NOT "%%I"=="" (
        ECHO Backing up database: %%I

        sqlcmd -S "%SQL_INSTANCE%" -U "%SQL_USER%" -P "%SQL_PASS%" -Q "BACKUP DATABASE [%%I] TO DISK='%BACKUP_PATH%\%NowDate%\%%I.bak' WITH INIT, COMPRESSION"

        ECHO Backup completed for: %%I
        ECHO.
    )
)

REM Remove temporary database list file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

ECHO All database backups completed.
ENDLOCAL

---

Steps to Run the Script

Step 1: Open Notepad

Open Notepad on the Windows server.

---

Step 2: Paste the Script

Copy and paste the above batch script into Notepad.

---

Step 3: Update SQL Details

Update the below values based on your server:

SET SQL_INSTANCE=.\SQLEXPRESS
SET SQL_USER=sa
SET SQL_PASS=YourPasswordHere

Example:

SET SQL_INSTANCE=.\SQLEXPRESS
SET SQL_USER=backupuser
SET SQL_PASS=StrongPassword

---

Step 4: Update Backup Path

Update the backup location if required.

SET BACKUP_PATH=D:\Backup

The backup files will be stored inside a date-wise folder like:

D:\Backup\04-13-2012

---

Step 5: Save the File

Save the file with .bat extension.

Example:

backup.bat

Make sure the file type is selected as All Files while saving.

---

Step 6: Run the Script

Right-click the backup.bat file and select Run as Administrator.

The script will list all user-created MSSQL databases and generate backup files in the backup folder.

---

Backup Output

After the script runs successfully, the backup files will be stored in the following format:

D:\Backup\DATE\DatabaseName.bak

Example:

D:\Backup\04-13-2012\mydatabase.bak

---

Important Notes

• Do not store SQL passwords in plain text on shared servers.
• Use a dedicated SQL backup user instead of the sa account whenever possible.
• Make sure the backup folder has enough disk space.
• Test the backup file by restoring it on a test server.
• Schedule the script using Windows Task Scheduler for automatic daily or weekly backups.
• Store an additional copy of the backup in another location for disaster recovery.

---

Conclusion

Using a batch script is a simple and effective way to take backups of all MSSQL databases on a Windows server. The script automatically collects the list of user databases, creates a date-wise backup folder, and stores each database as a .bak file.

Regular database backups help protect business-critical data and make it easier to recover from accidental deletion, database corruption, or server failure.