Scripted Backups Example 1

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