Execute the following SQL Server T-SQL example script in SSMS Query Editor to create and execute a stored procedure for backup up all databases.

-- SQL Server backup all databases - T-SQL backup database

-- SQL cursor - T-SQL stored procedure

USE AdventureWorks;

GO

CREATE PROC sprocBackupAllDatabases

AS

  BEGIN

    DECLARE  @DatabaseName SYSNAME,

             @SQLCommand   VARCHAR(1024)

    DECLARE curDBName CURSOR  FOR

    SELECT [name]

    FROM   MASTER..sysdatabases

    WHERE  [name] NOT IN ('model','tempdb')

    OPEN curDBName

    FETCH  curDBName

    INTO @DatabaseName

    WHILE (@@FETCH_STATUS = 0)

      BEGIN

        IF databasepropertyex(@DatabaseName,'Status') = 'online'

          BEGIN

            SELECT @SQLCommand = 'backup database ' + @DatabaseName +
            ' to disk=''e:\data\backup\' + @DatabaseName + '.dmp'' with noinit'

            PRINT @SQLCommand

            EXECUTE( @SQLCommand)

          END

        FETCH  curDBName

        INTO @DatabaseName

      END

    CLOSE curDBName

    DEALLOCATE curDBName

  END

GO

-- Execute stored procedure

EXEC sprocBackupAllDatabases

GO

/* Partial messages

backup database ReportServer$SQL2008 to disk='e:\data\backup\ReportServer$SQL2008.dmp' with noinit

backup database ReportServer$SQL2008TempDB to disk='e:\data\backup\ReportServer$SQL2008TempDB.dmp' with noinit

backup database AdventureWorks to disk='e:\data\backup\AdventureWorks.dmp' with noinit

backup database AdventureWorksDW to disk='e:\data\backup\AdventureWorksDW.dmp' with noinit

backup database AdventureWorksLT to disk='e:\data\backup\AdventureWorksLT.dmp' with noinit

backup database AdventureWorks2008 to disk='e:\data\backup\AdventureWorks2008.dmp' with noinit

backup database AdventureWorksDW2008 to disk='e:\data\backup\AdventureWorksDW2008.dmp' with noinit

backup database AdventureWorksLT2008 to disk='e:\data\backup\AdventureWorksLT2008.dmp' with noinit

*/