calendar Jan 1, 0001 · 7 min read  ·
Share on: linkedin copy

sql-server-script-to-backup-all-databases-to-disk.md

title: "SQL Server Script to Backup All Databases to Disk" description: "T-SQL script to back up all user databases to disk with dynamic date-stamped filenames. Automate full database backups in SQL Server using BACKUP DATABASE." keywords:

  • sql server backup all databases
  • backup database to disk
  • date stamped backup filenames
  • t-sql backup script
  • sql server automated backup
  • BACKUP DATABASE
  • sys.databases
  • sql server full backup
  • dba backup script
  • sql server database maintenance tags:
  • sql server
  • backup
  • database maintenance
  • t-sql
  • automation
  • sys.databases
  • dba scripts
  • full backup date: 2026-04-08 lastmod: 2026-04-11 showLastmod: true categories:
  • Scripts
  • SQL Server Backup Recovery series:
  • admin-guide featured: false codeMaxLines: 60 codeLineNumbers: true

Back Up All SQL Server User Databases with One Script

Backing up every user database manually is time-consuming and error-prone. This T-SQL script loops through all online user databases and writes a compressed full backup for each one to disk, using a date- and time-stamped filename so backups never overwrite each other.

Purpose and Overview

The script queries sys.databases to build a list of all online user databases, skipping the four system databases (master, model, msdb, and tempdb). It then constructs a filename that embeds the current date and time, and issues a BACKUP DATABASE statement for each database in sequence.

The result is a set of .bak files in a folder of your choice, each named in the pattern DatabaseName_YYYYMMDD_HHMM_db_full.bak. This naming convention makes it straightforward to identify when a backup was taken and which database it covers, without any external tooling.

Code Breakdown

The full script is shown below, followed by a section-by-section explanation.

 1-- Backup All User Databases to Disk with Date-Stamped Filenames
 2SET NOCOUNT ON;
 3
 4DECLARE @DbName        VARCHAR(100),
 5        @BackupStr     VARCHAR(1000),
 6        @BackupDate    VARCHAR(8),
 7        @BackupHour    VARCHAR(2),
 8        @BackupMinute  VARCHAR(2),
 9        @BackupTime    VARCHAR(18),
10        @BackupFolder  VARCHAR(128);
11
12-- Set your backup destination folder (trailing backslash required)
13SET @BackupFolder = 'C:\SQLBackups\';
14
15-- Build the date-time stamp for the filename
16SELECT @BackupDate   = CONVERT(VARCHAR(8), GETDATE(), 112),
17       @BackupHour   = DATEPART(HH, GETDATE()),
18       @BackupMinute = DATEPART(MI, GETDATE());
19
20-- Pad hour and minute to 2 digits
21IF LEN(@BackupHour)   = 1 SET @BackupHour   = '0' + @BackupHour;
22IF LEN(@BackupMinute) = 1 SET @BackupMinute = '0' + @BackupMinute;
23
24SET @BackupTime = '_' + @BackupDate + '_' + @BackupHour + @BackupMinute;
25
26-- Loop through all online user databases (excludes system databases and tempdb)
27DECLARE @Table TABLE (
28    RowId   TINYINT IDENTITY(1,1),
29    DbName  VARCHAR(100)
30);
31
32INSERT INTO @Table (DbName)
33SELECT name
34FROM   sys.databases
35WHERE  database_id > 4           -- exclude master, model, msdb, tempdb
36  AND  name <> 'tempdb'
37  AND  state = 0                 -- ONLINE
38  AND  state_desc = 'ONLINE';
39
40DECLARE @RowId TINYINT = 1,
41        @MaxId TINYINT = (SELECT MAX(RowId) FROM @Table);
42
43WHILE @RowId <= @MaxId
44BEGIN
45    SELECT @DbName = DbName FROM @Table WHERE RowId = @RowId;
46
47    SET @BackupStr = @BackupFolder + @DbName + @BackupTime + '_db_full.bak';
48
49    BACKUP DATABASE @DbName
50    TO DISK = @BackupStr
51    WITH STATS = 5, COMPRESSION;
52
53    SET @RowId = @RowId + 1;
54END;

Variable Declarations and Backup Folder

The script opens with SET NOCOUNT ON to suppress row-count messages that would otherwise clutter output. All working variables are declared in a single DECLARE block. The @BackupFolder variable holds the destination path and must end with a backslash. Change C:\SQLBackups\ to match your environment before running.

Building the Date-Time Stamp

CONVERT(VARCHAR(8), GETDATE(), 112) returns today's date in YYYYMMDD format — for example, 20260411. DATEPART(HH, ...) and DATEPART(MI, ...) return the current hour and minute as integers.

The two IF blocks zero-pad single-digit hours and minutes. Without padding, a backup run at 9:05 AM would produce _95 instead of _0905, which breaks lexicographic sort order. After padding, @BackupTime is assembled as _YYYYMMDD_HHMM.

Database Enumeration

A table variable @Table stores the list of eligible databases with an IDENTITY row counter. The INSERT ... SELECT statement pulls from sys.databases and applies three filters:

  • database_id > 4 — skips master (1), tempdb (2), model (3), and msdb (4)
  • name <> 'tempdb' — belt-and-suspenders guard against any edge case where database_id assignment differs
  • state = 0 and state_desc = 'ONLINE' — skips databases that are restoring, offline, suspect, or in recovery

The Backup Loop

A WHILE loop walks through the table variable one row at a time. For each database, the script builds the full destination path by concatenating @BackupFolder, the database name, @BackupTime, and the literal suffix _db_full.bak.

The BACKUP DATABASE statement uses two options:

  • STATS = 5 — prints a progress message every 5 percent, useful when running interactively or reviewing SQL Agent job history
  • COMPRESSION — compresses the backup file, which typically reduces size by 50–80 percent and speeds up the I/O-bound backup process on most workloads

Key Benefits and Use Cases

Single-script full backup. One execution backs up every user database without requiring individual BACKUP DATABASE statements for each database. This is especially useful on instances with many databases.

No filename collisions. The YYYYMMDD_HHMM timestamp in every filename means running the script multiple times per day produces distinct files. You can keep a rolling archive without worrying about overwriting yesterday's backup.

Offline and problem databases are skipped. The state = 0 filter means the script will not fail if one database is in a restoring or suspect state. Only ONLINE databases are backed up.

Easy scheduling. The script runs as a single SQL Agent job step with no external dependencies. Schedule it nightly or as needed without any PowerShell or batch file wrapper.

Compression included. Backup compression is on by default. On SQL Server 2008 and later (Enterprise, Standard from 2008 R2+), this reduces backup size and total runtime with no extra configuration.

Performance Considerations

Sequential execution. The script backs up databases one at a time. On instances with many large databases, total runtime can be long. If parallel backups are required, consider splitting the script or using a SQL Agent multi-step job that targets specific databases.

I/O contention. All backups write to the same folder, which means all I/O goes to one disk or share. If backup performance is a concern, consider striping each backup across multiple files using multiple TO DISK clauses, or directing different databases to different backup volumes.

TINYINT row counter limit. The @RowId and @MaxId variables are declared as TINYINT, which has a maximum value of 255. If your instance has more than 255 user databases, change these to SMALLINT or INT.

Backup compression availability. WITH COMPRESSION requires SQL Server 2008 Enterprise or SQL Server 2008 R2 Standard and later. On editions that do not support compression, remove the COMPRESSION keyword.

Running during business hours. Full backups are read-consistent but generate significant I/O. On busy OLTP systems, schedule backups during low-traffic windows to avoid degrading query performance.

Practical Tips

Change the backup folder. Update SET @BackupFolder = 'C:\SQLBackups\'; to point to a valid, writable path on your server. A UNC path to a network share works as long as the SQL Server service account has write permission.

Test with a single database first. Before scheduling, run the script manually and confirm that one .bak file appears in the destination folder with the expected filename format.

Verify the backup after writing. Add RESTORE VERIFYONLY FROM DISK = @BackupStr inside the loop after the BACKUP DATABASE statement to confirm each backup file is readable before moving on.

Exclude specific databases. Add AND name NOT IN ('DatabaseToSkip1', 'DatabaseToSkip2') to the WHERE clause of the INSERT INTO @Table statement to exclude databases you do not want to back up.

Integrate with SQL Agent. Paste the script into a SQL Agent job step of type "Transact-SQL script." Set the job to run on the schedule you need. Check job history for the STATS = 5 output to confirm backups are completing.

Adjust retention separately. This script writes backups but does not delete old ones. Use a separate maintenance job, a PowerShell script, or SQL Agent's built-in cleanup task to remove backup files older than your retention window.

Conclusion

This script provides a straightforward way to back up every online user database on a SQL Server instance with a single execution. The date-time-stamped filenames keep backups organized, the state = 0 filter prevents failures on offline databases, and the built-in compression option reduces storage and I/O overhead. It is a practical starting point for any DBA who needs a lightweight, schedulable backup routine without the complexity of a full maintenance framework.

References