Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1174 Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1367

Backups are one of the most important tasks when maintaining a system, and as the complexity of your environment grows, you need tools to automate tasks. Using the SQL Agent, you can schedule the following stored procedure to back up every database and database log on your server (or modify it to your needs), excluding some stuff you can live without.
 

CREATE PROCEDURE BackupDatabases
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @name VARCHAR(50) /*database name*/
    DECLARE @dbpath VARCHAR(256) /*path for backup db files*/
    DECLARE @logpath VARCHAR(256) /*path for backup log files*/
    DECLARE @dbName VARCHAR(256) /*filename for backup*/
    DECLARE @logName VARCHAR(256) /*filename for backup*/
    DECLARE @fileDate VARCHAR(20) /*used for file name*/

    SET @dbpath = 'C:Database Backup'
    SET @logpath = 'C:Database Log Backup'

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM master.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb') /* Exclude any databases you don't want to backup */

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
           SET @dbName = @dbpath + @name + '_' + @fileDate + '.BAK'
           BACKUP DATABASE @name TO DISK = @dbName WITH FORMAT;

           SET @logName = @logpath + @name + '_' + @fileDate + '.BAK'
           BACKUP LOG @name TO DISK = @logName;

           FETCH NEXT FROM db_cursor INTO @name 
    END 

    CLOSE db_cursor 
    DEALLOCATE db_cursor
END
GO