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.
Was this article helpful?
Let me know so I can keep improving.
