/*******************************************************************\
*               Copyright © Pro-DBA.com 2006, all rights reserved   *
*                                                                   *
* Name     :    BackupDatabases.sql                                 *
*                                                                   *
* Purpose  :    Backs up all databases (except tempdb) to specified *
*               file location                                       *
*                                                                   *
* Tested on:    SQL Server 7.0, SQL Server 2000, SQL Server 2005    *
*                                                                   *
* Usage    :    Edit @backupPath value to equal preferred file      *
*               location                                            *
*                                                                   *
\*******************************************************************/

DECLARE @DBID INT
DECLARE @DBName SYSNAME
DECLARE @sqlCmd NVARCHAR(1000)
DECLARE @backupPath NVARCHAR(255)
DECLARE @cDateTime DATETIME

--Change backup path as required
SET @backupPath = '''C:\MSSQL\BACKUP\'

SET @cDateTime = GETDATE()

SELECT @DBID = MIN(DBID) 
FROM master.dbo.sysdatabases 
WHERE [DBID] > 0 and name <> 'tempdb'

WHILE @DBID IS not null
BEGIN

   SELECT @sqlCmd = 'BACKUP DATABASE ' + QUOTENAME(DB_NAME(@DBID)) + ' TO DISK = ' + @backupPath + DB_NAME(@DBID) + CONVERT(NVARCHAR(6),@cDateTime,12) + '.BAK'' WITH INIT'
   
   PRINT @sqlCmd

   EXEC sp_executesql @sqlCmd

   SELECT @DBID = MIN(DBID) 
   FROM master.dbo.sysdatabases 
   WHERE [DBID] > 0 and DBID > @DBID AND name <> 'tempdb'

END