Back up all MS SQL databases at once

This article will discuss how to backup all MS SQL databases with one script. A separate file will be created for each database.

1. Log into your server through Remote Desktop Connection (instructions for connecting to your server through RDC can be found here).
2. Open SQL Server Management Studio and select the server name
3. Click the New Query button and enter in the following data:

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:\Backup\' 
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') 
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 

4. Make sure that the directory in the SET @path line exists. If the directory (in this case C:\Backup) does not exist, create the directory else the script will fail
5. Click the Execute! button and the script will execute
6. Once finished, a dialog box will appear stating such. Now all databases are backed up in C:\Backup with the database name as the file name