Simple script to backup all SQL Server databases

With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this. 

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

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

File Naming Format DBname_YYYYDDMM_HHMMSS.BAK

If you want to also include the time in the filename you can replace this line in the above script:

-- specify filename format

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

with this line:

-- specify filename format

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

Notes

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.