Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
You can backup all databases on an Instance with the help of the following script (except tempdb)
You should write the path you want to backup instead of “C:\Backup”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | DECLARE @name VARCHAR(50) -- Database Name DECLARE @path VARCHAR(256) -- Backup Path DECLARE @fileName VARCHAR(256) -- Backup File Name DECLARE @fileDate VARCHAR(20) --Date to add to backup file name --database backup directory SET @path = 'C:\Backup' -- filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('tempdb') -- exclude 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 WITH COPY_ONLY FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor |
