/*
Easy SQL backups
Joe Scott, Digital Incite and Matter Ltd
joe.scott@digitaliandm.com
*/

DECLARE @bakPath NVARCHAR(250)
DECLARE @dbName NVARCHAR(50)
DECLARE @bakDate NVARCHAR(20)
DECLARE @bakPrefix NVARCHAR(50)

SET @bakDate = CONVERT(date,GETDATE())
SET @bakPrefix = 'BACKUP'

USE [master]

DECLARE FindDatabases CURSOR FOR
SELECT Name FROM sys.databases
WHERE (Name LIKE 'MyDatabase%')
AND State = 0

OPEN FindDatabases

FETCH NEXT FROM FindDatabases INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN
	SET @bakPath = @bakPrefix + '_' + @dbName + '_' + @bakDate + '.bak'
	PRINT('Backing up database "'+ @dbName + '" to path: ' + @bakPath)
	BACKUP DATABASE @dbName TO DISK=@bakPath WITH COPY_ONLY, COMPRESSION, STATS=10
    FETCH NEXT FROM FindDatabases INTO @dbName
END

CLOSE FindDatabases
DEALLOCATE FindDatabases