Scripted backup for all SQL databases on a server

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