Script to Backup All SQL Server Database

Question

Here is the script that will allow you to backup each database within your instance of SQL Server. You will need to change the @path to the appropriate backup directory.

File Naming Format DBname_YYYYDDMM_HHMMSS.bak

    DECLARE @name VARCHAR(50) -- database name  
    DECLARE @path VARCHAR(256) -- path for backup files  
    DECLARE @fileName VARCHAR(256) -- filename for backup  
    DECLARE @fileDate VARCHAR(20) -- used for file name     

    -- specify database backup directory
    SET @path = 'C:\Backup\'          

    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')        

    DECLARE db_cursor CURSOR FOR  
    SELECT name 
    FROM master.dbo.sysdatabases 
    WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases        

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name           

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
           SET @fileName = @path + @name + '_' + @fileDate + '.bak'  
           BACKUP DATABASE @name TO DISK = @fileName          

           FETCH NEXT FROM db_cursor INTO @name   
    END           

    CLOSE db_cursor   
    DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Also, if you wanted to bypass some of your user databases you can include them in the NOT IN section as well.