Simple script to backup all SQL Server databases

Problem

Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to backup all databases on your server.   This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server.  You could use SQL Server Management Studio to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

Solution

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.

This post covers two topics --  First, it is a very simple method to backup all of the user databases. Secondly, it is an example of a cursor.  I typically stay away from cursors within any real data manipulations, but I don't mind using them for simple functions such as this. The tSQL is a very simple BACKUP command, and the cursor just allows us to walk through all of the databases with that command, very quickly and very efficiently.  

/*

Use cursor to backup all user databases.  */

DECLARE 

@DBName VARCHAR(25),   -- database name  

@BkupPath VARCHAR(255),   -- the path for backup files  

@BkupFileName VARCHAR(255),   -- the backup filename

@FileDate VARCHAR(10)   -- to put date into filename

 

-- Where is the backup directory?

SET @BkupPath = 'C:\MSSQL\Backup\'  

 

-- Construct the BkupFileName

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

 

-- Declare your cursor

DECLARE DBCursor CURSOR FOR  

SELECT name 

FROM master.dbo.sysdatabases 

WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude the system databases

 

-- Open your cursor

OPEN DBCursor   

FETCH NEXT FROM DBCursor INTO @DBName   

 

WHILE @@FETCH_STATUS = 0   

BEGIN   

   SET @BkupFileName = @BkupPath + @DBName + '_' + @FileDate + '.BAK'  

   BACKUP DATABASE @DBName TO DISK = @BkupFileName  

 

   FETCH NEXT FROM DBCursor INTO @DBName   

END   

 

-- Close your cursor

CLOSE DBCursor   

DEALLOCATE DBCursor

Note, you can revise the resulting .bak filename, if you'd like something different than 'DatabaseName_YYYYMMDD.bak'.  Also note the WHERE clause on the cursor declaration. You could adjust this to IN or NOT IN, and customize the database list to whatever databases you'd like to target.  This is the clause that I used in my test:     WHERE name IN ('master','model','msdb')

And this is my output:

Take a look at that run time.  All three sys dbs backed up to in 5 seconds.  Not bad.

That's pretty much it.  Like I said, you should modify that WHERE clause to suit your needs.  This is a very flexible, easily customized routine for backing up multiple databases via cursor. 

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

or

Here is an update to the script to also include the time in the filename.

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

SET @path = 'C:\'

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 IN ('test5')

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

or

without cursors.

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName NVARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
 
SET @path = 'd:\Backup\' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SELECT name,flag=0 into #tempbackup FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
set rowcount 1
WHILE (exists(SELECT * FROM #tempbackup WHERE flag=0))
 BEGIN 
       Select @name=name from #tempbackup WHERE flag=0
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName        
       Update #tempbackup set flag=1 WHERE flag=0
 END  
set rowcount 0
drop table #tempbackup
 

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.