USE master
GO
DECLARE @dbs AS TABLE ( id int identity(1,1), dbname sysname )
DECLARE @id int
, @dbname sysname
, @path varchar(128)
, @file nvarchar(255)
, @theday char(1)
SET @path = ‘D:DEV_BACKUPS’
SET @theday = datepart(dw, CURRENT_TIMESTAMP)
INSERT INTO @dbs ( dbname )
SELECT name
FROM sys.databases
WHERE database_id > 4 –not system dbs
AND state = 0 –online
ORDER BY name
SELECT @id = max(id) FROM @dbs
WHILE @id > 0
BEGIN
SELECT @dbname = dbname FROM @dbs WHERE id = @id
SET @file = @path + ” + @dbname + ‘_BAK’ + @theday + ‘.bak’
BACKUP DATABASE @dbname TO DISK = @file WITH INIT;
BACKUP LOG @dbname WITH TRUNCATE_ONLY;
SET @id = @id – 1
END
GO