How To: Backup All Databases at Once

This article can be used when needing to take backups quickly for all the Passageways databases in the event an update or server migration will be taking place. Follow the steps below to back up all the Passageways databases. 

1. Log on to the SQL server for the Passageways Portal. 

2. Make sure the path below in red is created on the server or updated to reflect the appropriate folder location. Currently listed as 'C:\Migration', and also that the blue 'Production_Portal%' matches your prefix on your databases names.

3. Run the script below. 

4. Verify the folder location has populated with the database backups. 

DECLARE

@name VARCHAR(150), -- database name
@path VARCHAR(256), -- path for backup files
@fileName VARCHAR(256), -- filename for backup
@fileDate VARCHAR(20) -- used for file name

CREATE TABLE [dbo].#tempBackup (name VARCHAR(200), flag BIT)

SET @path = 'C:\Migration\'
-- Includes the date in the filename
SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

-- Includes the date and time in the filename
--SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '')

INSERT
INTO [dbo].#tempBackup (name, flag)
SELECT name, 0
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') and name like 'Production_Portal%' --This may be removed if your databases are not named with the Production_Portal naming convention. 
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

WHILE EXISTS(SELECT TOP 1 name FROM [dbo].#tempBackup WHERE flag = 0)
BEGIN

SELECT
@name = name,
@fileName = @path + name + '_' + @fileDate + '.BAK' -- Unique FileName
--@fileName = @path + @name + '.BAK' -- Non-Unique Filename
FROM [dbo].#tempBackup
WHERE flag = 0

-- does not overwrite the existing file
BACKUP DATABASE @name TO DISK = @fileName

-- overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique
--BACKUP DATABASE @name TO DISK = @fileName WITH INIT
UPDATE #tempBackup
SET flag = 1
WHERE name = @name
AND flag = 0
END

DROP TABLE [dbo].#tempBackup