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
