SQL Server:Backup multiple databases Using with Scripts (Include Backup history)
Here is the script that will allow you to backup each database within your instance of SQL Server. You will need to change the @bupath to the appropriate backup directory.
DECLARE @dbname VARCHAR(100) -- database name
DECLARE @bupath VARCHAR(100) -- path for backup location
DECLARE @filename VARCHAR(100) -- filename used for backup files
DECLARE @datestamp VARCHAR(25) -- date used for backup file timestamp
-- specify database backup directory
SET @bupath = 'D:\DatabaseBackups\'
-- file date formatting
SELECT @datestamp = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
--specify databases to backup
DECLARE db_cursor CURSOR for
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
-- excludes these databases, if excluding multiple databases, seprate them by a comma
--backup process
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @filename = @bupath + @dbname + '_' + @datestamp + '.bak'
BACKUP DATABASE @dbname TO DISK = @filename WITH INIT,
CHECKSUM; --init overwrites existing files with the same name, and checksum verifies the backup
FETCH NEXT from db_cursor INTO @dbname
END
CLOSE db_cursor
--backup History for All Dbs
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(mi, COALESCE((SELECT TOP 1 bsPrior.backup_finish_date
FROM msdb.dbo.backupset bsPrior WHERE bs.database_name = bsPrior.database_name AND bs.backup_finish_date > bsPrior.backup_finish_date
ORDER BY bsPrior.backup_finish_date DESC), '1900/1/1'), bs.backup_finish_date) AS minutes_since_last_backup,
DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes,
CASE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date)
WHEN 0 THEN 0 ELSE CAST(( bs.backup_size / ( DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) ) / 1048576 ) AS INT) END AS throughput_mb_sec
FROM msdb.dbo.backupset bs WHERE database_name = @dbname AND bs.backup_start_date > DATEADD(dd, -14, GETDATE()) ORDER BY bs.backup_start_date
--select @dbname from msdb.dbo.backupset
FETCH NEXT from db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
|
OUTPUT:
