SQL Script to Backup all SQL Server Databases
Backup all databases on your server using T-SQL is a much simpler and faster. Below T-SQL script will generate
backup of all databases with date stamp in backup file name. Update the backup path in below script.
DECLARE @dbname NVARCHAR(256)
DECLARE @filepath NVARCHAR(512)
DECLARE @fileName NVARCHAR(512)
DECLARE @Date NVARCHAR(40)
-- Database backup directory
SET @filepath = 'D:\backup\'
-- filename format
SELECT @Date = CONVERT(NVARCHAR(20),GETDATE(),110)
DECLARE backup_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude system databases
AND state = 0 -- Online database
AND is_in_standby = 0 -- database is not read only for log shipping
OPEN backup_cursor
FETCH NEXT FROM backup_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @filepath + @dbname + '_' + @Date + '.bak'
BACKUP DATABASE @dbname TO DISK = @fileName WITH INIT
FETCH NEXT FROM backup_cursor INTO @dbname
END
CLOSE backup_cursor
DEALLOCATE backup_cursor
