QT - Back up every MSSQL database on a server with one script

Part 2 of 2 in a series
Quick Tips

If you want a quick way to back up every MSSQL database on a box without clicking through them one by one, this does it. It loops through the user databases, writes a separate .bak file for each one, and works fine as a basic SQL Agent job or scheduled maintenance script.

DECLARE @name sysname;
DECLARE @path VARCHAR(256);
DECLARE @fileName VARCHAR(512);
DECLARE @fileDate VARCHAR(20);

-- Backup directory
SET @path = 'C:\Backup\';

-- Filename format: YYYYMMDD_HHMMSS
SELECT @fileDate = CONVERT(VARCHAR(8), GETDATE(), 112) + '_' +
                   REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '');

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
  AND state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @fileName = @path + @name + '_' + @fileDate + '.bak';

    BACKUP DATABASE @name
    TO DISK = @fileName
    WITH INIT, CHECKSUM;

    FETCH NEXT FROM db_cursor INTO @name;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

What it does
This script skips the system databases, goes through the online user databases, and writes each backup to C:\Backup\ with the database name and a timestamp in the filename.

Before you run it
Make sure the backup folder already exists, and make sure the SQL Server service account can actually write to it. That part trips people up more often than the SQL itself.

Quick note
This is a handy baseline, not a full backup strategy. It does not deal with retention, cleanup, restore testing, or alerting. Good enough for a quick job, not the last word on MSSQL backups.

share

Was this article helpful?

Let me know so I can keep improving.

Related articles

🗄️
Hypervisors
QT - Proxmox VE
Two quick Proxmox VE tips: run the post-install helper script after a fresh setup, then reset an LXC container password directly from the host when you need fast access recovery.