Generate Backup Scripts

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author: ChillyDBA

Date: 6 Jun 2020

Description

Generates TSQL Scripts to backup all user databases on the source server to a local folder

GENERATE script on: SOURCE SERVER

EXECUTE script on: SOURCE SERVER

Code


--*******************************************--**** RUN THIS ON THE SOURCE SERVER ***--*******************************************

-- use this script to generate backup scripts for every non-system DB on a server.-- save each DB script to a separate file using the file name at the top of the section
SET NOCOUNT ON
--*******************************************--**** Define and set parameter values ***--*******************************************

DECLARE @DatabaseName SYSNAME ,@DatabaseID INT ,@FilePath SYSNAME

DECLARE @BackupDatabaseList TABLE( DatabaseID INT ,DatabaseName SYSNAME)
DECLARE @BackupDatabaseResults TABLE( ResultID INT IDENTITY (1,1) ,ResultText SYSNAME)
SELECT @FilePath = 'D:\DBA\Temp\' --supply a path name to use for the backup command ,@DatabaseName = NULL --optionally, supply a DB name for generaton of command for a single database

INSERT @BackupDatabaseList (DatabaseID, DatabaseName)SELECT dbid, nameFROM master..sysdatabasesWHERE name NOT IN ('master','model','tempdb','msdb','reportserver','reportservertempdb', 'distribution')AND name = ISNULL(@DatabaseName, name)AND status & 512 = 0 -- not offlineAND status & 4096 = 0 -- not single user mode

SELECT @DatabaseID = MIN (DatabaseID)FROM @BackupDatabaseList
WHILE @DatabaseID IS NOT NULLBEGIN SELECT @DatabaseName = DatabaseName FROM @BackupDatabaseList WHERE DatabaseID = @DatabaseID

 INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @BackupDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ START SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @BackupDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT '--**********************************************************************************' INSERT @BackupDatabaseResults (ResultText) SELECT '--***** Purpose: Backup Database [' + @DatabaseName + '] ready for migration' INSERT @BackupDatabaseResults (ResultText) SELECT '--***** Generated on: ' + CONVERT(VARCHAR(12), GETDATE(), 112) INSERT @BackupDatabaseResults (ResultText) SELECT '--***** Generated by: ' + SUSER_SNAME() INSERT @BackupDatabaseResults (ResultText) SELECT '--**********************************************************************************' INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT 'USE [' + @DatabaseName + ']' INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT 'BACKUP DATABASE [' + @DatabaseName + ']' INSERT @BackupDatabaseResults (ResultText) SELECT 'TO DISK = N''' + @FilePath + @DatabaseName + '_For_Migration.bak''' INSERT @BackupDatabaseResults (ResultText) SELECT 'WITH' INSERT @BackupDatabaseResults (ResultText) SELECT ' COPY_ONLY,' INSERT @BackupDatabaseResults (ResultText) SELECT ' NOFORMAT,' INSERT @BackupDatabaseResults (ResultText) SELECT ' INIT,' INSERT @BackupDatabaseResults (ResultText) SELECT ' NAME = N''' + @DatabaseName + '-FULL Database Backup'',' INSERT @BackupDatabaseResults (ResultText) SELECT ' SKIP,' INSERT @BackupDatabaseResults (ResultText) SELECT ' NOREWIND,' INSERT @BackupDatabaseResults (ResultText) SELECT ' NOUNLOAD,' INSERT @BackupDatabaseResults (ResultText) SELECT ' STATS = 5' INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT ' GO' INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @BackupDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ END SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @BackupDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT '' INSERT @BackupDatabaseResults (ResultText) SELECT ''

 SELECT @DatabaseID = MIN (DatabaseID) FROM @BackupDatabaseList WHERE DatabaseID > @DatabaseID
END
SELECT ResultTextFROM @BackupDatabaseResultsORDER BY ResultID
SET NOCOUNT OFF

030. Generate backup scripts.sqlDisplaying 030. Generate backup scripts.sql.