A one-time copy needs a script you can inspect before it runs. Generating backup commands with STRING_AGG puts every selected database into one ordered, reviewable batch.
Define the Copy's Purpose
This script prepares full copy-only backups of online user databases. It does not configure scheduling, retention, encryption, or a complete recovery chain. Those responsibilities remain part of the established backup strategy.
I generate the commands before executing any backup. I also compare the selected database list with the intended copy request. A command generator is useful only when its selection is equally reviewable.
A copy-only full backup avoids changing the differential base used by regular differential backups. It still consumes storage, processor time, and I/O. Keep the one-time copy coordinated with existing maintenance and workload activity.
CHECKSUM adds integrity checks during backup and stores backup checksum information. It does not establish that a restore will succeed under every later condition. The recovery test still matters after the files exist.
The backup directory belongs to the SQL Server computer. A path visible in your SSMS workstation is irrelevant when the server cannot access it. The service account needs permission to create the files there.
Review the Inventory Before Generating Backup Commands
Select databases whose state is ONLINE and whose identifier exceeds four. That excludes the system databases from this particular copy request. It does not automatically exclude every database unsuitable for a normal backup.
SELECT database_id, name, state_desc, source_database_id,
is_read_only, recovery_model_desc
FROM sys.databases
WHERE state_desc = N'ONLINE'
AND database_id > 4
ORDER BY name;Database snapshots appear separately and cannot be backed up with BACKUP DATABASE. The generator below therefore excludes rows with source_database_id populated. Keep that exclusion visible rather than waiting for a generated statement to fail.
An availability configuration also changes the correct backup location. Review replicas, backup preferences, and the specific version's supported operations before running this instance-local script. This simple inventory does not implement that routing policy.
Permissions can hide databases from the inventory. Use an account authorized to inspect the requested set and perform its backups. An empty generated result means nothing was selected, not that everything was copied.
Confirm that the target has enough capacity before execution. No query in this generator establishes available disk space. Use the expected copy scope and your current storage evidence to make that decision.
Generating Backup Commands Safely
STRING_AGG requires SQL Server 2017 or later. WITHIN GROUP supplies the database-name ordering instead of relying on the input scan order. Use a database compatibility level that supports the ordered aggregate.
The input expression is converted to nvarchar(max) before aggregation. Converting only the finished result is too late to avoid the aggregate's fixed-width limit. Database identifiers and file literals also require different escaping rules.
DECLARE @Folder nvarchar(1000) = N'D:\SqlBackups\';
DECLARE @Stamp nvarchar(30) =
CONVERT(nvarchar(8), GETDATE(), 112) + N'_' +
REPLACE(CONVERT(nvarchar(12), GETDATE(), 114), N':', N'');
IF RIGHT(@Folder, 1) <> N'\'
THROW 50001, 'The backup folder must end with a backslash.', 1;
CREATE TABLE #BackupCommands
(
DatabaseID int NOT NULL PRIMARY KEY,
DatabaseName sysname NOT NULL,
BackupPath nvarchar(2000) NOT NULL,
CommandText nvarchar(max) NOT NULL
);
INSERT #BackupCommands(DatabaseID, DatabaseName, BackupPath, CommandText)
SELECT d.database_id, d.name, p.BackupPath,
CAST(N'BACKUP DATABASE ' AS nvarchar(max)) + QUOTENAME(d.name)
+ N' TO DISK = N''' + REPLACE(p.BackupPath, N'''', N'''''')
+ N''' WITH COPY_ONLY, CHECKSUM;'
FROM sys.databases AS d
CROSS APPLY
(
VALUES(@Folder + N'database_' + CONVERT(nvarchar(11), d.database_id)
+ N'_' + @Stamp + N'.bak')
) AS p(BackupPath)
WHERE d.state_desc = N'ONLINE'
AND d.database_id > 4
AND d.source_database_id IS NULL;
DECLARE @Script nvarchar(max);
SELECT @Script = STRING_AGG(CAST(CommandText AS nvarchar(max)),
CHAR(13) + CHAR(10))
WITHIN GROUP (ORDER BY DatabaseName)
FROM #BackupCommands;
SELECT DatabaseID, DatabaseName, BackupPath
FROM #BackupCommands ORDER BY DatabaseName;
SELECT @Script AS ReviewableBackupScript;
DECLARE @Position int = 1;
WHILE @Position <= LEN(@Script)
BEGIN
PRINT SUBSTRING(@Script, @Position, 4000);
SET @Position += 4000;
END;The filename uses the database identifier and a date-time stamp. This avoids copying characters from database names into Windows filenames. Keep the accompanying inventory because an identifier alone is not a descriptive archive label.
QUOTENAME protects the SQL database identifier, including names containing spaces or closing brackets. REPLACE escapes apostrophes inside the file string. Neither function validates the actual directory or the Windows service permissions.
STRING_AGG ignores null inputs. Here the required command columns prevent missing commands from silently entering the staging table. Review the selected inventory and command count together before treating the script as complete.
Retrieve the Complete Script
PRINT truncates a single nvarchar message beyond its supported length. The loop emits smaller chunks so the complete value remains available. The SELECT result also depends on the client's configured output limits.
Do not assume a visible cell contains the entire generated script. Increase the SSMS results limit or retrieve individual command rows. Verify that the last selected database has its complete closing statement.
The commands currently exist only as text. Nothing in the generator executes them, and its inventory SELECT is not a backup result. Review the folder, selected databases, and every generated statement before proceeding.
A beautifully ordered script can still fill the wrong drive. Keep the target review practical and specific. Confirm both the intended directory and the free space before the first file is created.
Run Only the Reviewed Copy
After review, execute the approved statements in a separate deliberate step. The guarded example below runs one selected command from the staging table. Set the identifier only after checking that command and its target path. As written, the block stops with error 50002 on purpose until you set that value.
DECLARE @ApprovedDatabaseID int = NULL;
DECLARE @ReviewedSql nvarchar(max);
IF @ApprovedDatabaseID IS NULL
THROW 50002, 'Choose an explicitly reviewed database identifier.', 1;
SELECT @ReviewedSql = CommandText
FROM #BackupCommands
WHERE DatabaseID = @ApprovedDatabaseID;
IF @ReviewedSql IS NULL
THROW 50003, 'The approved database is not in this generated list.', 1;
EXEC sys.sp_executesql @ReviewedSql;Does the copy request include a defined verification step? Check the backup completion messages and inspect the resulting files. Then perform the appropriate verification and a test restore for the intended recovery use.
Review Failure and File Reuse
Generating backup commands does not reserve the filenames. Another process can create a file before execution, and an existing backup file can contain multiple sets. Confirm the target policy before reusing any path.
This example omits INIT and FORMAT so it does not deliberately overwrite existing backup media. Do not add those options as a casual cleanup. A mistaken overwrite destroys the earlier backup sets stored in that file.
A failing command also needs a recorded outcome. Permissions, insufficient space, and a changed database state require different responses. Keep the error message beside the selected database instead of assuming later statements repaired the failure.
The staging table exists only in the current session. Closing that session removes the generated inventory. Save the reviewed script and the database-to-file mapping before handing the copy to someone else.
Use backup header inspection to identify the set written. Retain the position if a file contains several sets. A file's name describes your intention, while its header identifies the backup available for restore.
Know the Limits of Generating Backup Commands
Generating backup commands gives you a consistent one-time script. It does not provide retries, alerting, retention, or protection against an unavailable target. Maintain those controls in the regular backup process.
The inventory can change between generation and execution. Recheck the selected databases if the script waits before use. A changed state, renamed database, or failover deserves a fresh review.
Retain the approved commands and their results beside the copied files. Record what completed rather than marking every generated line successful. A reviewable copy is useful when its execution evidence is equally clear.
Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and STRING_AGG Function to Concatenate Strings.
A generated backup script is not a recovery strategy, it is a reviewed copy operation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
