Part of the DBA-Tools Project.
Stop Hand-Typing Database Names Into Backup Commands
Writing a one-off BACKUP DATABASE or RESTORE DATABASE command for a single database is trivial. Doing it correctly for every database on an instance, with a consistent naming convention, the right options, and none typed by hand, is where mistakes creep in: a missed database, a typo in a path, a differential backup issued for a database that’s never had a full one to diff against.
These four scripts generate the actual BACKUP/RESTORE T-SQL for every online user database on the instance, ready to review and run, rather than asking you to write and re-type it every time. Full, differential, transaction log, and restore, one generator each, sharing the same conventions so the output reads consistently across all four.
Why Generate Backup and Restore Scripts Matters
- Generated scripts eliminate the two most common manual-backup mistakes: a database left out because someone forgot it existed, and a typo in a hand-typed path or database name
- Every generated script uses
@ts, a variable that resolves to the actual execution timestamp inside the generated script, not the moment you ran the generator, so the filename always reflects when the backup or restore actually happened, not when you built the command - The transaction log generator specifically filters to FULL/BULK_LOGGED databases only, since log backups aren’t valid for SIMPLE recovery, a distinction easy to get wrong by hand across dozens of databases
- Reviewing generated T-SQL before running it is safer than a black-box “backup everything” job: you see exactly what will execute, against which databases, with which options, before anything runs
When to Run These Scripts
- Setting up backup jobs on a newly built or newly inherited instance, where no consistent backup convention exists yet
- Building an ad-hoc backup before a risky change (a migration, a schema change, a patch), across every database in one pass rather than one at a time
- Preparing a restore script ahead of a DR test or an actual recovery, so the exact commands are ready and reviewed rather than written under pressure
- Any time the standard backup path or options need to change across the whole instance, generated fresh rather than hand-edited into an existing job
The Scripts
All four follow the same shape: set the backup path and options in a handful of variables at the top, then generate one BACKUP/RESTORE statement per online user database.
Generate-FullBackupScript — Full Backups for Every Database
/*
Script Name : Generate-FullBackupScript
Category : backups-and-recovery
Purpose : Generate a FULL backup script for all online user databases.
@ts in the generated script resolves at execution time so filenames
include the backup timestamp, not the script generation timestamp.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-generate-backup-and-restore-scripts/)
Requires : VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-generate-backup-and-restore-scripts/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @BackupPath nvarchar(260) = N'D:\SQL-Backups';
DECLARE @Compression bit = 1;
DECLARE @StatsInterval int = 5;
IF RIGHT(@BackupPath, 1) = N'\' SET @BackupPath = LEFT(@BackupPath, LEN(@BackupPath) - 1);
DECLARE @WithClause nvarchar(200) = N'WITH ';
SET @WithClause += CASE WHEN @Compression = 1 THEN N'COMPRESSION, ' ELSE N'' END;
SET @WithClause += N'STATS = ' + CAST(@StatsInterval AS nvarchar(3)) + N';';
DECLARE @cmd nvarchar(max) =
N'-- FULL backup script, ' + @@SERVERNAME + CHAR(13) + CHAR(10) +
N'-- Path : ' + @BackupPath + CHAR(13) + CHAR(10) +
N'-- Verify path exists before executing.' + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10) +
N'DECLARE @ts varchar(15) = FORMAT(GETDATE(), ''yyyyMMdd_HHmmss'');' + CHAR(13) + CHAR(10) +
N'DECLARE @path nvarchar(500);' + CHAR(13) + CHAR(10);
SELECT @cmd +=
CHAR(13) + CHAR(10) +
N'SET @path = ''' + @BackupPath + N'\' + d.name + N'_FULL_'' + @ts + ''.bak'';' + CHAR(13) + CHAR(10) +
N'BACKUP DATABASE [' + d.name + N'] TO DISK = @path ' + @WithClause + CHAR(13) + CHAR(10)
FROM sys.databases AS d
WHERE d.database_id > 4
AND d.state_desc = N'ONLINE'
ORDER BY d.name;
IF @cmd IS NULL OR @cmd = N''
SET @cmd = N'-- No online user databases found.' + CHAR(13) + CHAR(10);
SELECT @cmd AS script;
Generate-DiffBackupScript — Differential Backups
/*
Script Name : Generate-DiffBackupScript
Category : backups-and-recovery
Purpose : Generate a DIFFERENTIAL backup script for all online user databases.
@ts in the generated script resolves at execution time so filenames
include the backup timestamp, not the script generation timestamp.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-generate-backup-and-restore-scripts/)
Requires : VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-generate-backup-and-restore-scripts/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @BackupPath nvarchar(260) = N'D:\SQL-Backups';
DECLARE @Compression bit = 1;
DECLARE @StatsInterval int = 5;
IF RIGHT(@BackupPath, 1) = N'\' SET @BackupPath = LEFT(@BackupPath, LEN(@BackupPath) - 1);
DECLARE @WithClause nvarchar(200) = N'WITH DIFFERENTIAL, ';
SET @WithClause += CASE WHEN @Compression = 1 THEN N'COMPRESSION, ' ELSE N'' END;
SET @WithClause += N'STATS = ' + CAST(@StatsInterval AS nvarchar(3)) + N';';
DECLARE @cmd nvarchar(max) =
N'-- DIFFERENTIAL backup script, ' + @@SERVERNAME + CHAR(13) + CHAR(10) +
N'-- Path : ' + @BackupPath + CHAR(13) + CHAR(10) +
N'-- Requires a prior FULL backup for each database.' + CHAR(13) + CHAR(10) +
N'-- Verify path exists before executing.' + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10) +
N'DECLARE @ts varchar(15) = FORMAT(GETDATE(), ''yyyyMMdd_HHmmss'');' + CHAR(13) + CHAR(10) +
N'DECLARE @path nvarchar(500);' + CHAR(13) + CHAR(10);
SELECT @cmd +=
CHAR(13) + CHAR(10) +
N'SET @path = ''' + @BackupPath + N'\' + d.name + N'_DIFF_'' + @ts + ''.bak'';' + CHAR(13) + CHAR(10) +
N'BACKUP DATABASE [' + d.name + N'] TO DISK = @path ' + @WithClause + CHAR(13) + CHAR(10)
FROM sys.databases AS d
WHERE d.database_id > 4
AND d.state_desc = N'ONLINE'
ORDER BY d.name;
IF @cmd IS NULL OR @cmd = N''
SET @cmd = N'-- No online user databases found.' + CHAR(13) + CHAR(10);
SELECT @cmd AS script;
Generate-TLogBackupScript — Transaction Log Backups (FULL/BULK_LOGGED Only)
/*
Script Name : Generate-TLogBackupScript
Category : backups-and-recovery
Purpose : Generate a transaction log backup script for all online user databases
in FULL or BULK_LOGGED recovery model. SIMPLE recovery databases are
excluded — log backups are not supported for them.
@ts in the generated script resolves at execution time so filenames
include the backup timestamp, not the script generation timestamp.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-generate-backup-and-restore-scripts/)
Requires : VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-generate-backup-and-restore-scripts/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @BackupPath nvarchar(260) = N'D:\SQL-Backups';
DECLARE @Compression bit = 1;
DECLARE @StatsInterval int = 5;
IF RIGHT(@BackupPath, 1) = N'\' SET @BackupPath = LEFT(@BackupPath, LEN(@BackupPath) - 1);
DECLARE @WithClause nvarchar(200) = N'WITH ';
SET @WithClause += CASE WHEN @Compression = 1 THEN N'COMPRESSION, ' ELSE N'' END;
SET @WithClause += N'STATS = ' + CAST(@StatsInterval AS nvarchar(3)) + N';';
DECLARE @cmd nvarchar(max) =
N'-- TRANSACTION LOG backup script, ' + @@SERVERNAME + CHAR(13) + CHAR(10) +
N'-- Path : ' + @BackupPath + CHAR(13) + CHAR(10) +
N'-- FULL and BULK_LOGGED databases only (SIMPLE excluded).' + CHAR(13) + CHAR(10) +
N'-- Verify path exists before executing.' + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10) +
N'DECLARE @ts varchar(15) = FORMAT(GETDATE(), ''yyyyMMdd_HHmmss'');' + CHAR(13) + CHAR(10) +
N'DECLARE @path nvarchar(500);' + CHAR(13) + CHAR(10);
SELECT @cmd +=
CHAR(13) + CHAR(10) +
N'SET @path = ''' + @BackupPath + N'\' + d.name + N'_LOG_'' + @ts + ''.trn'';' + CHAR(13) + CHAR(10) +
N'BACKUP LOG [' + d.name + N'] TO DISK = @path ' + @WithClause + CHAR(13) + CHAR(10)
FROM sys.databases AS d
WHERE d.database_id > 4
AND d.state_desc = N'ONLINE'
AND d.recovery_model_desc IN (N'FULL', N'BULK_LOGGED')
ORDER BY d.name;
IF @cmd IS NULL OR @cmd = N''
SET @cmd = N'-- No eligible databases found. All online user databases may be in SIMPLE recovery.' + CHAR(13) + CHAR(10);
SELECT @cmd AS script;
Generate-RestoreScript — Restore Databases from Backup
/*
Script Name : Generate-RestoreScript
Category : backups-and-recovery
Purpose : Generate a RESTORE DATABASE script for all online user databases.
Set @ts to the timestamp of the backup files you want to restore
before executing. Review WITH MOVE if restoring to a different server.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-generate-backup-and-restore-scripts/)
Requires : VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-generate-backup-and-restore-scripts/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @BackupPath nvarchar(260) = N'D:\SQL-Backups';
DECLARE @WithReplace bit = 1; -- 1 = WITH REPLACE
DECLARE @WithNoRecovery bit = 0; -- 1 = WITH NORECOVERY (log chain)
DECLARE @StatsInterval int = 5;
IF RIGHT(@BackupPath, 1) = N'\' SET @BackupPath = LEFT(@BackupPath, LEN(@BackupPath) - 1);
DECLARE @WithClause nvarchar(200) = N'WITH ';
SET @WithClause += CASE WHEN @WithReplace = 1 THEN N'REPLACE, ' ELSE N'' END;
SET @WithClause += CASE WHEN @WithNoRecovery = 1 THEN N'NORECOVERY, ' ELSE N'' END;
SET @WithClause += N'STATS = ' + CAST(@StatsInterval AS nvarchar(3)) + N';';
DECLARE @cmd nvarchar(max) =
N'-- RESTORE script, ' + @@SERVERNAME + CHAR(13) + CHAR(10) +
N'-- Path : ' + @BackupPath + CHAR(13) + CHAR(10) +
N'-- Set @ts to the timestamp of the backup files to restore.' + CHAR(13) + CHAR(10) +
N'-- Review WITH MOVE if restoring to a different server or drive.' + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10) +
N'DECLARE @ts varchar(15) = ''yyyyMMdd_HHmmss''; -- replace with actual backup timestamp' + CHAR(13) + CHAR(10) +
N'DECLARE @path nvarchar(500);' + CHAR(13) + CHAR(10);
SELECT @cmd +=
CHAR(13) + CHAR(10) +
N'SET @path = ''' + @BackupPath + N'\' + d.name + N'_FULL_'' + @ts + ''.bak'';' + CHAR(13) + CHAR(10) +
N'RESTORE DATABASE [' + d.name + N'] FROM DISK = @path ' + @WithClause + CHAR(13) + CHAR(10)
FROM sys.databases AS d
WHERE d.database_id > 4
AND d.state_desc = N'ONLINE'
ORDER BY d.name;
IF @cmd IS NULL OR @cmd = N''
SET @cmd = N'-- No online user databases found.' + CHAR(13) + CHAR(10);
SELECT @cmd AS script;
Each one returns a single nvarchar(max) result, the generated script itself, ready to copy out, review, and run.
How To Run From The Repo
Clone DBA Tools, initialize and run whichever generator you need:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Generate each script type:
.\run.ps1 Generate-FullBackupScript
.\run.ps1 Generate-DiffBackupScript
.\run.ps1 Generate-TLogBackupScript
.\run.ps1 Generate-RestoreScript
# To run against a remote sql server:
.\run.ps1 Generate-FullBackupScript -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/backups/Generate-FullBackupScript.sqlsql/backups/Generate-DiffBackupScript.sqlsql/backups/Generate-TLogBackupScript.sqlsql/backups/Generate-RestoreScript.sql
Example Output
Real generated output from this lab instance, not staged, trimmed to the first couple of databases for readability (the real output continues one block per online database):
-- FULL backup script, HPAI01
-- Path : D:\SQL-Backups
-- Verify path exists before executing.
DECLARE @ts varchar(15) = FORMAT(GETDATE(), 'yyyyMMdd_HHmmss');
DECLARE @path nvarchar(500);
SET @path = 'D:\SQL-Backups\DemoDatabase_FULL_' + @ts + '.bak';
BACKUP DATABASE [DemoDatabase] TO DISK = @path WITH COMPRESSION, STATS = 5;
SET @path = 'D:\SQL-Backups\GrowthLab_FULL_' + @ts + '.bak';
BACKUP DATABASE [GrowthLab] TO DISK = @path WITH COMPRESSION, STATS = 5;
The transaction log generator correctly excluded every SIMPLE-recovery database on this instance and only produced statements for the FULL-recovery ones, DemoDatabase and GrowthLab both appear here, matching the same two databases Recovery Model Audit flags as FULL recovery with no log backups yet, real cross-script consistency, not a coincidence:
-- TRANSACTION LOG backup script, HPAI01
-- Path : D:\SQL-Backups
-- FULL and BULK_LOGGED databases only (SIMPLE excluded).
-- Verify path exists before executing.
DECLARE @ts varchar(15) = FORMAT(GETDATE(), 'yyyyMMdd_HHmmss');
DECLARE @path nvarchar(500);
SET @path = 'D:\SQL-Backups\DemoDatabase_LOG_' + @ts + '.trn';
BACKUP LOG [DemoDatabase] TO DISK = @path WITH COMPRESSION, STATS = 5;
SET @path = 'D:\SQL-Backups\GrowthLab_LOG_' + @ts + '.trn';
BACKUP LOG [GrowthLab] TO DISK = @path WITH COMPRESSION, STATS = 5;
Understanding the Results
- @ts resolves inside the generated script, not the generator — every filename gets the timestamp of when the backup or restore actually runs, not when you generated the script, this matters if you generate once and run later, or reuse a generated script across multiple runs
- The differential and log generators depend on a full backup already existing — a differential with no prior full, or a log backup on a database with no full backup chain established, will fail at execution time, this is expected, the generator doesn’t verify backup history for you
- The transaction log generator silently skips SIMPLE-recovery databases — if a database you expected to see is missing from the generated log backup script, check its recovery model first with Recovery Model Audit, it’s very likely SIMPLE
- The restore generator’s
@tsis a placeholder, not a real timestamp — it has to be manually set to the actual timestamp of the backup files you’re restoring from, there’s no way for the generator to know that in advance
Best Practices
- Always review generated T-SQL before running it, especially the restore script,
WITH REPLACEwill overwrite an existing database of the same name - Keep the backup path variable consistent across all four generators, mismatched paths between backup and restore generation are an easy, avoidable mistake
- Generate the log backup script fresh any time a new database is added to the instance, rather than manually editing an existing scheduled job, so newly-FULL-recovery databases aren’t silently missed
- Use Recovery Model Audit alongside these generators to confirm every database that should have a log backup chain actually has one building correctly
Related Scripts
You may also find these scripts useful:
- Backup Chain Integrity
- Backup Encryption Status
- Backup Restore Duration Estimate
- Backup Size Trend
- Database Backup History
- Recovery Model Audit
Frequently Asked Questions
Why generate the script instead of just scheduling a backup job directly?
A generated script is reviewable before it runs, you see exactly which databases and options are included. It’s also a good starting point for a scheduled job once you’re happy with it, generate once, then wrap the reviewed output in a SQL Agent job step.
Can I use these against a database with existing backup jobs already configured?
Yes, they’re read-only generators, they don’t touch backup history or existing jobs, they just produce T-SQL text based on the current database list and recovery models. Running the generator doesn’t affect anything until you actually execute the generated output.
Summary
Backup and restore commands are simple in isolation and error-prone at scale, one database missed, one path typo, one differential attempted against a database with no full backup behind it. Generating the T-SQL for every online database in one pass, with consistent conventions across full, differential, log, and restore, removes most of that risk before a single command runs.
Review the generated output before executing it, keep backup paths consistent across all four generators, and re-generate the log backup script whenever a new database joins the instance, so nothing gets silently missed.
Leave a Reply