Scripted backup for all SQL databases on a server

Created by Jeremy Burgess, Modified on Tue, 28 Nov, 2023 at 10:54 AM by Jeremy Burgess

Symptoms

You need to back up all databases using a SQL command instead of SSMS UI.

Cause

n/a

Resolution

-- Declare variables
DECLARE @DatabaseName NVARCHAR(255);
DECLARE @BackupPath NVARCHAR(255);
DECLARE @FileName NVARCHAR(255);
DECLARE @Command NVARCHAR(1000);

-- Set the backup path
SET @BackupPath = 'C:\BackupFolder\' 

-- Cursor to loop through databases, excluding system databases
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')

-- Open cursor and fetch first database
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

-- Loop through databases
WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Set the filename
    SET @FileName = @BackupPath + @DatabaseName + '.bak'

    -- Prepare the backup command
    SET @Command = 'BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ''' + @FileName + ''''

    -- Execute the backup command
    EXEC (@Command)

    -- Fetch next database
    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Clean up
CLOSE db_cursor  
DEALLOCATE db_cursor