The below SQL will provide the backup database instructions needed. It is working on MSSQL 2000 – > 2008
Modify the @PATH variable as needed.
declare @PATH VARCHAR(1000)
SELECT @PATH='\\server_name\database_backup\'
DECLARE @SRVNAME VARCHAR(100)
SELECT @SRVNAME=COALESCe(@@SERVERNAME, 'server_not_defined')
select 'backup database ' + name + ' to disk=''' + @path + @SRVNAME + '_' + name + '.bak'' ' from master..sysdatabases where name not in ('master','tempdb','model','msdb','pubs','northwind')