-- Copyright © Microsoft Corporation.All Rights Reserved.
-- This code released under the terms of the
-- Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
-- $Id$ --
USE [master]
GO
/**
 * Object: StoredProcedure [dbo].[sp_BackupDatabases]
 * Script Date: 16/02/2023
 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================================
-- Author: Microsoft
-- Author: Eriidan
-- Create date: 2010-02-06
-- Modification: 2023-02-16
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- Parameter4: add date and time marker to file name or not
-- ====================================================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases]
	@databaseName SYSNAME = NULL,
	@backupType CHAR(1),
	@backupLocation NVARCHAR(200),
	@addDateTime BIT = 0
AS
SET NOCOUNT ON
SET DATEFORMAT ymd
DECLARE @RetVal INT
-- RetVal : 1 = Failure; 0 = Success
SET @RetVal = 1
DECLARE @DBs TABLE
(
	ID INT IDENTITY PRIMARY KEY,
	DBNAME NVARCHAR(500)
)
-- Pick out only databases which are online in case ALL databases are chosen to be backed up
-- If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT [Name] FROM [master].[sys].[databases]
WHERE (STATE = 0)
AND ([Name] = ISNULL(@databaseName, [Name]))
ORDER BY Name
-- Filter out databases which do not need to backed up 
IF (@backupType = 'F')
	DELETE @DBs WHERE DBNAME IN ('master', 'model', 'msdb', 'tempdb')
ELSE IF (@backupType = 'D')
	DELETE @DBs WHERE DBNAME IN ('master', 'model', 'msdb', 'tempdb')
ELSE IF (@backupType = 'L')
	DELETE @DBs WHERE DBNAME IN ('master', 'model', 'msdb', 'tempdb')
ELSE
	RETURN @RetVal
-- Declare variables
DECLARE @BackupName NVARCHAR(100)
DECLARE @BackupFile NVARCHAR(300)
DECLARE @DBNAME NVARCHAR(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop INT
-- Loop through the databases one by one
SELECT @Loop = MIN(ID) FROM @DBs
SET @RetVal = 0
WHILE (@Loop IS NOT NULL)
BEGIN
	-- Database Names have to be in [dbname] format since some have - or _ in their name
	SET @DBNAME = '[' + (SELECT DBNAME FROM @DBs WHERE (ID = @Loop)) + ']'
	-- Set the current date and time yyyyMMdd_hhmmss format
	SET @dateTime = FORMAT(GETDATE(), 'yyyyMMdd_hhmmss')
	-- Create backup filename in path\filename.extension format for full,diff and log backups
	IF (@addDateTime = 1)
	BEGIN
		IF (@backupType = 'F')
			SET @BackupFile = @backupLocation + REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + '_FULL_' + @dateTime + '.bak'
		ELSE IF (@backupType = 'D')
			SET @BackupFile = @backupLocation + REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + '_DIFF_' + @dateTime + '.bak'
		ELSE IF (@backupType = 'L')
			SET @BackupFile = @backupLocation + REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + '_LOG_' + @dateTime + '.trn'
	END
	ELSE
	BEGIN
		IF (@backupType = 'F')
			SET @BackupFile = @backupLocation + REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + '_FULL.bak'
		ELSE IF (@backupType = 'D')
			SET @BackupFile = @backupLocation + REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + '_DIFF.bak'
		ELSE IF (@backupType = 'L')
			SET @BackupFile = @backupLocation + REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + '_LOG_.trn'
	END
	-- Provide the backup a name for storing in the media 
	IF (@backupType = 'F')
		SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup at '+ @dateTime
	IF (@backupType = 'D')
		SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup at '+ @dateTime
	IF (@backupType = 'L')
		SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup at '+ @dateTime
	-- Generate the dynamic SQL command to be executed
	IF (@backupType = 'F')
		SET @sqlCommand = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME= '''+ @BackupName + ''', NOSKIP, NOFORMAT'
	IF (@backupType = 'D')
		SET @sqlCommand = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''' + @BackupFile + ''' WITH DIFFERENTIAL, INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'
	IF (@backupType = 'L')
		SET @sqlCommand = 'BACKUP LOG ' + @DBNAME + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'
	-- Execute the generated SQL command 
	EXEC(@sqlCommand) 
	-- Goto the next database 
	SELECT @Loop = MIN(ID) FROM @DBs WHERE (ID > @Loop)
END
RETURN @RetVal
GO