Share via

Backup multiple SQL databases in a single swoop!

  • Article

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!

Comments