Backup all MSSQL databases

1. Log into your server through Remote Desktop Connection.
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