Today I will share a simple but powerful stored procedure that allows me to quickly backup all databases on a SQL Server to a single location. We usually execute it before performing risky operations like hardware upgrades or service pack installs. The stored procedure is very simple, it's really just a single statement that calls the sp_MSForEachDB procedure. This stored procedure takes an SQL command as a parameter, replaces all occurences of ? with the database name and executes the command. It's one of my favorite system stored procedures even though it's considered undocumented/unsupported by Microsoft. SQL Server also has a similar stored procedure called sp_MSForEachTable.
Here is the script, you just need to modify the disk location:
USE master
GO
CREATE PROCEDURE sp_QuickBackup
AS
EXEC sp_MSForEachDB 'IF ''?'' <> ''tempdb'' BACKUP DATABASE [?] TO
DISK = ''c:\SQL Backups\?.bak'''
GO