Mostly so I can link it to a post that’s getting traffic…
Declare @DBname varchar(255),
@SQL nvarchar(max),
@today nvarchar(50),
@servername nvarchar(50)
set @today = convert(nvarchar,GETDATE(),12)
set @servername = REPLACE(@@SERVERNAME,'\','-')
select @DBname = min(name) from sys.databases where name <> 'tempdb' and name <> 'model'
while @DBname is not null
set @SQL='BACKUP DATABASE [' + @DBName + '] TO DISK = N''c:\backups\sql\' + @servername + '-' + @DBName + '-' + @today + '.bak'' WITH FORMAT, INIT, NAME = N''Full ' + @DBName + 'Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
--print @SQL
EXEC SP_EXECUTESQL @SQL
select @DBname = min(name) from sys.databases where (name <> 'tempdb' and name <> 'model') and name > @DBname
END
(Inspired by continued traffic on my old Windows Internal Database/SQL Express Backups and Maintenance post.)
