Take copy_only backups

 How to take copy only backup for all backups

declare @OutputDir varchar(255)='<path>'--Give the path

DECLARE @name VARCHAR(50) -- database name

    DECLARE @path VARCHAR(256) -- path for backup files

    DECLARE @fileName VARCHAR(256) -- filename for backup

    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = @OutputDir

    --SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)

       SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

    PRINT 'Starting Backups'

    DECLARE db_cursor CURSOR FOR

        SELECT name FROM MASTER.dbo.sysdatabases

            --WHERE name like ('')--Change the db’s according to your requirements

                     --NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')

 

        OPEN db_cursor

            FETCH NEXT FROM db_cursor INTO @name

            WHILE @@FETCH_STATUS = 0 BEGIN

                SET @fileName = @path + @name + '_'+'0000'+'_' + @fileDate + '.BAK'

                    PRINT 'Starting Backup For ' + @name

                    BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT, copy_only, compression

                                  print @name + 'backup complete'

                FETCH NEXT FROM db_cursor INTO @name

            END

        CLOSE db_cursor

    DEALLOCATE db_cursor

    PRINT 'Backups Finished'

To find Database Size in GBs,TBs

 Use below T-Sql command to find the database size in GBs,TB's

--Database Sizes

select

'server' = @@servername,d.NAME

, 'total size in megabytes'= convert(decimal(10,2),(sum(size * 8.00) / 1024.00 ))

, 'total size in gigabytes' = convert(decimal(10,2),(sum(size * 8.00) / 1024.00 / 1024.00))

, 'total size in terabytes' = convert(decimal(10,2),(sum(size * 8.00) / 1024.00 / 1024.00 / 1024.00)) from

sys.master_files mf

INNER JOIN sys.databases d ON d.database_id = mf.database_id

WHERE d.database_id > 4

and d.is_read_only<>1

GROUP BY d.NAME

ORDER BY d.NAME