How to backup all the user databases

The database administrator must backup periodically the databases on the Sql Servers.

The process is tedious and must be automated with a script to avoid forgetting any database.


The simple script can be scheduled on the Sql Server agent.

DECLARE @backupName VARCHAR(255) -- database backup name  
DECLARE @databaseName VARCHAR(255) -- database name  
DECLARE @path VARCHAR(256) -- Folder for the for backup files  
DECLARE @fileName VARCHAR(256) -- file name for generating the backup  
DECLARE @databaseExclusions TABLE (DatabaseName VARCHAR(255)) -- Databases to exclude the backup

-- Input parameters
SET @path = 'D:\Backup\2\'  
INSERT INTO @databaseExclusions (DatabaseName)
VALUES
('tempdb'),('master'),('model'),('msdb'),('AdventureWorks')


DECLARE database_cursor CURSOR FOR  
SELECT top 5 name
    FROM sys.databases 
    WHERE [state] <> 6 -- OFFLINE
    AND owner_sid != 1 -- User user database
    AND name NOT IN (SELECT DatabaseName FROM @databaseExclusions)
 
OPEN database_cursor   
FETCH NEXT FROM database_cursor INTO @databaseName   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @databaseName;  
   SET @backupName = @databaseName + '-Full Database Backup';

   BACKUP DATABASE @databaseName TO DISK = @fileName WITH NOFORMAT, INIT,  NAME = @backupName, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
   
   FETCH NEXT FROM database_cursor INTO @databaseName   
END   

 
CLOSE database_cursor   
DEALLOCATE database_cursor