You know as well as I do, there are many ways toaccomplish this task…some even easier, however, in attempts to use SCORCH more frequently, I decided to make a RunBook to which performed the job for me. Using SCORCH’s “Query Database” activity, I pasted the following code in the query window and set the connection parameters and off it went. You can copy the code below and run it in a SSMS query window and it will work as well.
As always, make sure you check the product documentation, check with Microsoft support and test in a lab environment prior to implementing anything in a production environment.
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
SET @bupath = ‘F:\DatabaseBackups\’
SELECT @datestamp = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE
DECLARE db_cursor CURSOR for
WHERE name NOT IN (‘tempdb’) — excludes these databases, if excluding multiple databases, seprate them by a comma
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
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
That’s all for now, thanks for reading!