USE master GO
/**
* author: 
*	jianming.wjm@alibaba-inc.com of Alibaba Cloud Team.
*
* @purpose:
*	Backup your databases to local directory (no matter destination folder exist or not).
*	You can personal your backup type, support three backup types include(FULL, DIFF, LOG).
*	This script also support just check to see details or run directly.
*
*	if you find something happen unhappy, please kindly tell us via the author email. thanks.
* 
* @usage:
*	Copy all the script (ctrl + a) into SSMS, fill out the public variables and then F5 or Execute it.
*
* @parameters:
*	@backup_databases_list: databases list needed to backup
*	@backup_type: which backup type needed, choice one of ['FULL', 'DIFF', 'LOG']
*	@backup_folder: where backup files located
*	@is_run: I want to run or just check, 1: run; 0: just check.
* 
* @version:
*	1.0.0, 2018/03/02 15:20: Create script.
*
* @Tips:
*	We'd like to advice you to change your databases to FULL recovery model to improve migrate to RDS.
*	example: ALTER DATABASE [db_simple] SET RECOVERY FULL WITH NO_WAIT
*	pay attention to the SSMS output messages, and correct the error if have any.
**/

SET
    NOCOUNT ON
    -- declare public variables
    DECLARE @backup_databases_list = N'test_migr',
    @backup_type = N'FULL',
    @backup_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\FULL.bak',
    @is_run = 1;

-- YOU HAVE TO INIT PUBLIC VARIABLES HERE!!!!
SELECT
    /**
    * Databases list needed to backup, delimiter is ; or ,
    * empty('') or null: means all databases excluding system database
    * example: '[testdb];TestDR,Test,readonly'
    **/
    @backup_databases_list = N'[TestMigration];[TestFG],readonly,Test',
    @backup_type = N'FULL', -- Backup Type? FULL: FULL backup; DIFF: Differential backup; LOG: Log backup
    @backup_folder = N'C:\BACKUP', -- Backup folder to store backup files.
    @is_run = 0 -- Check or run? 1, run directly; 0, just check
;

-- variables checking
IF @backup_type NOT IN(N'FULL', N'DIFF', N'LOG') BEGIN RAISERROR (
    'Backup type %s is not supported!', 16, 1, @backup_type
)
RETURN END;

IF ISNULL(@backup_folder, N'') = N'' BEGIN RAISERROR (
    'backup_folder is not allow empty!', 16, 1
)
RETURN END

IF @is_run NOT IN(0, 1) BEGIN RAISERROR (
    'is_run parameter should be 0 or 1 !', 16, 1
)
RETURN END
-- declare private variables
DECLARE @database_name SYSNAME, @database_state TINYINT, @database_state_desc NVARCHAR (60), @database_count INT, @database_do INT, @flag_edition BIT, @exec_sql NVARCHAR (MAX), @timestamp VARCHAR(20), @version_major FLOAT, @edition SYSNAME, @context_info VARCHAR(20), @backup_databases_list_XML XML;

-- private variables init.
SELECT
	@database_name = N'',
	@database_state = 11,
	@database_state_desc = N'',
	@database_count = 0,
	@database_do = 1,
	@version_major = CASE PARSENAME(CAST(SERVERPROPERTY('productversion') AS nvarchar(128)), 4)
						WHEN 7 THEN 7.0
						WHEN 8 THEN 2000
						WHEN 9 THEN 2005
						WHEN 10 THEN
									CASE  PARSENAME(CAST(SERVERPROPERTY('productversion') AS nvarchar(128)), 3)
										WHEN 0 THEN 2008
										WHEN 50 THEN 2008.5
										ELSE 0.0
									END
						WHEN 11 THEN 2012
						WHEN 12 THEN 2014
						WHEN 13 THEN 2016
						WHEN 14 THEN 2017
						WHEN 15 THEN 2019
						ELSE 0.0
					END,
	@edition = CONVERT(sysname, SERVERPROPERTY(N'Edition')),
	@flag_edition = 
						CASE
							WHEN @version_major < 2008 THEN 0 -- lower then SQL 2008, fg: SQL 2000 / 2005
							WHEN @version_major >= 2008 and @version_major < 2012 -- SQL 2008 & 2008R2
							THEN 
								CASE
									WHEN @edition LIKE N'Enterprise%' OR @edition LIKE N'Developer%' THEN 1
									ELSE 0
								END
							WHEN @version_major >= 2012 -- 2012 or later on
							THEN 
								CASE
									WHEN @edition LIKE N'Enterprise%' OR @edition LIKE N'Developer%' OR @edition LIKE N'Standard%' THEN 1
									ELSE 0
								END
							ELSE 0
						END,
	@exec_sql = N'',
	@timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), GETDATE(),120), N'-', N''), N':', N''), CHAR(32), N''),
	@backup_folder = CASE 
						WHEN RIGHT(@backup_folder, 1) = N'\' THEN @backup_folder 
						ELSE @backup_folder + N'\' 
					END,
	@context_info = N'0x1256698449',
	@backup_databases_list_XML = 
			N'<V><![CDATA[' + 
				REPLACE(
					REPLACE(
						REPLACE(
								REPLACE(
										REPLACE(
												REPLACE(
														@backup_databases_list, '[', ''
													), ']', ''
											), ';', ','
									),CHAR(10),']]></V><V><![CDATA['
								),',',']]></V><V><![CDATA['
							),CHAR(13),']]></V><V><![CDATA['
					) 
				+ ']]></V>'
;

-- temp table declare to save databases splitted
IF OBJECT_ID('tempdb..#tmp_databases', 'U') IS NOT NULL
	DROP TABLE #tmp_databases
CREATE TABLE #tmp_databases(
	RowId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Database_name SYSNAME
)
;

-- get databases name
IF ISNULL(@backup_databases_list, N'') > N''
BEGIN
	INSERT INTO #tmp_databases
	SELECT DISTINCT
		[Database_name] = T.C.value('(./text())[1]','sysname')
	FROM @backup_databases_list_XML.nodes('./V') AS T(C)
	WHERE T.C.value('(./text())[1]','sysname') IS NOT NULL
	;

END ELSE BEGIN
INSERT INTO #tmp_databases
SELECT DISTINCT
    name
FROM sys.databases
WITH (NOLOCK)
WHERE
    name NOT IN(
        N'master', N'tempdb', N'model', N'msdb', N'distribution'
    ) END
-- decalre cursor to loop each database.
DECLARE CUR_DatabaseList CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT DISTINCT name, state, state_desc
FROM sys.databases WITH (NOLOCK)
WHERE name IN(SELECT [Database_name] FROM #tmp_databases)

-- Open cursor and get the rowcount
OPEN CUR_DatabaseList FETCH NEXT
FROM
    CUR_DatabaseList INTO @database_name,
    @database_state,
    @database_state_desc
SET
    @database_count = @@CURSOR_ROWS;

-- prepare folder on local host.
IF @database_count > 0 BEGIN EXEC sys.xp_create_subdir @backup_folder END

WHILE @@FETCH_STATUS = 0 BEGIN
-- contact the backup script for each backup type.
SET
    @exec_sql = CASE
        WHEN @backup_type = N'FULL' THEN N'BACKUP DATABASE ' + QUOTENAME (@database_name) + N' TO DISK = N' + QUOTENAME (
            @backup_folder + @database_name + N'_' + @backup_type + N'_' + @timestamp + N'.bak', ''''
        ) + N' WITH STATS = 1' + CASE
            WHEN @flag_edition = 1 THEN N', COMPRESSION;'
            ELSE N';'
        END
        WHEN @backup_type = N'DIFF' THEN N'BACKUP DATABASE ' + QUOTENAME (@database_name) + N' TO DISK = N' + QUOTENAME (
            @backup_folder + @database_name + N'_' + @backup_type + N'_' + @timestamp + N'.bak', ''''
        ) + N' WITH DIFFERENTIAL, STATS = 1' + CASE
            WHEN @flag_edition = 1 THEN N', COMPRESSION;'
            ELSE N';'
        END
        WHEN @backup_type = N'LOG' THEN N'BACKUP LOG ' + QUOTENAME (@database_name) + N' TO DISK = N' + QUOTENAME (
            @backup_folder + @database_name + N'_' + @backup_type + N'_' + @timestamp + N'.bak', ''''
        ) + N' WITH STATS = 1' + CASE
            WHEN @flag_edition = 1 THEN N', COMPRESSION;'
            ELSE N';'
        END
        ELSE N''
    END;

RAISERROR (
    '--==============%d/%d: Working on database: %s', 10, 1, @database_do, @database_count, @database_name
)
WITH
    NOWAIT;

--Only online database can be Backup.
IF @database_state = 0 BEGIN RAISERROR ('-- %s', 10, 1, @exec_sql)
WITH
    NOWAIT;

IF @is_run = 1 BEGIN
SET
    @exec_sql = N'SET CONTEXT_INFO ' + @context_info + N';' + @exec_sql;

EXEC sys.sp_executesql @exec_sql END END ELSE BEGIN RAISERROR (
    '-- database %s state: %s can''t be backup, skipped.', 10, 1, @database_name, @database_state_desc
)
WITH
    NOWAIT;

END SET @database_do = @database_do + 1;

FETCH NEXT
FROM
    CUR_DatabaseList INTO @database_name,
    @database_state,
    @database_state_desc END
    -- close and release cursor.
    CLOSE CUR_DatabaseList DEALLOCATE CUR_DatabaseList GO