Generate a backup commands

If you have several database and you want to create a copy only backup from them the following script help you to generate a commands

DECLARE @DESTDIR VARCHAR(80);

SET @DESTDIR='C:\DIR1\';

/*------------------------------------------------------------------------*/
DECLARE @SQL nvarchar(max), @dbName sysname;
    DECLARE DBcursor CURSOR  FOR
    SELECT name FROM     master.dbo.sysdatabases
    WHERE  name NOT IN ('master','tempdb','model','msdb')
      AND  DATABASEPROPERTYEX(name,'status')='ONLINE' ORDER BY name;
    OPEN DBcursor; FETCH  DBcursor   INTO @dbName;
    WHILE (@@FETCH_STATUS = 0) -- loop through all db-s
      BEGIN
        
SET @SQL = 'BACKUP DATABASE ['+ @dbName +'] TO  DISK = N'''+@DESTDIR+''+@dbName +'_copy_only.bak'' WITH COPY_ONLY';
        
PRINT @SQL;
PRINT 'GO';
        FETCH  DBcursor INTO @dbName;
     END; -- while
   CLOSE DBcursor; DEALLOCATE DBcursor;


Result:

BACKUP DATABASE [Database_1] TO  DISK = N'C:\DIR1\Database_1_copy_only.bak' WITH COPY_ONLY
GO
BACKUP DATABASE [Database_2] TO  DISK = N'C:\DIR1\Database_2_copy_only.bak' WITH COPY_ONLY
GO
BACKUP DATABASE [Database_3] TO  DISK = N'C:\DIR1\Database_3_copy_only.bak' WITH COPY_ONLY
GO
BACKUP DATABASE [Database_4] TO  DISK = N'C:\DIR1\Database_4_copy_only.bak' WITH COPY_ONLY
GO
BACKUP DATABASE [Sales] TO  DISK = N'C:\DIR1\Sales_copy_only.bak' WITH COPY_ONLY
GO
BACKUP DATABASE [test1] TO  DISK = N'C:\DIR1\test1_copy_only.bak' WITH COPY_ONLY
GO