Backup SQL Server databases with T-SQL

 1DECLARE @name VARCHAR(50) -- database name
 2DECLARE @path VARCHAR(256) -- path for backup files
 3DECLARE @fileName VARCHAR(256) -- filename for backup
 4DECLARE @fileDate VARCHAR(20) -- used for file name
 5DECLARE @debug BIT = 0
 6
 7-- specify database backup directory
 8SET @path = 'c:\temp\db-backup\' --must exist on disk
 9
10-- specify filename format
11SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
12
13DECLARE db_cursor CURSOR FOR
14SELECT name
15FROM master.dbo.sysdatabases
16WHERE name NOT IN ('tempdb') -- exclude these databases
17
18OPEN db_cursor
19FETCH NEXT FROM db_cursor INTO @name
20
21	WHILE @@FETCH_STATUS = 0
22	BEGIN
23		SET @fileName = @path + @name  + '.BAK'
24		if @debug = 0 BACKUP DATABASE @name TO DISK = @fileName with init, format, COMPRESSION
25		if @debug = 1 print 'backup ' + @name + ' to ' + @fileName
26
27		FETCH NEXT FROM db_cursor INTO @name
28	END
29
30CLOSE db_cursor
31DEALLOCATE db_cursor