DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to backup all databases?

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

-- SQL Server full database backup basic syntax
BACKUP DATABASE [AdventureWorks2008] TO  DISK = N'F:\data\backup\aw8.bak'
GO
-- SQL Server full database backup with options
BACKUP DATABASE [AdventureWorks2008] TO  DISK = N'F:\data\backup\aw8.bak'
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2008-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
------------
-- SQL Server transaction log backup basic syntax
BACKUP LOG [AdventureWorks2008] TO  DISK = N'F:\data\backup\aw8_20120201_1430.trn'
GO
------------
-- SQL Server differential backup basic syntax
BACKUP DATABASE [AdventureWorks2008] TO  DISK = N'F:\data\backup\aw8.dif'
WITH  DIFFERENTIAL
GO
------------

 

-- 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

*/

 

Related articles:

Simple script to backup all SQL Server databases

Backup all databases on a server

http://www.sqlusa.com/bestpractices/lastdatabasebackupdate/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE