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!

 
Comments (4)

  1. Glenn Wilson says:

    These are two Blog posts that I have put together that may also help.

    Powershell: Script to Backup all SQL Server Databases on a Server Instance, virtualrealm.com.au/…/Powershell-Script-to-Backup-all-SQL-Server-Databases-on-a-Server

    Powershell: Script to Remove Old SQL Server Backup Files, virtualrealm.com.au/…/using-powershell-to-remove-old-sql-server-backup-files

    These could also be executed from a Runbook in SCORCH.

  2. Hey Glenn, thanks for sharing! It's always great to see other peoples approaches to solutions.

  3. thomas says:

    Thanks for such a simple query that does the trick so nicely!

  4. SQL Developer says:

    THanks for saving ton of time.

Skip to main content