Backup multiple SQL databases in a single swoop!
This is a query I use to backup all of my lab databases. I use System Center 2012 Orchestrator (SCORCH) to kick off a runbook which executes this query againsy my SQL servers to backup all of the databases.
You know as well as I do, there are many ways to accomplish 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.
]------ Begin Code ------[
--declaring variables
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 = 'F:\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 ('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
DEALLOCATE db_cursor
]------ End Code ------[
That's all for now, thanks for reading!