Generate Backup/Restore to URL Azure Data Studio Notebook using PowerShell

DECLARE @StorageAccount      VARCHAR (MAX)  = '<<StorageAccount>>'
      , @Container           VARCHAR (100)  = '<<Container>>'
      , @Replace             BIT            = '<<REPLACE>>'
      , @BackupQueryOptions  NVARCHAR (MAX) = N'  WITH COMPRESSION, STATS = 5 '
      , @RestoreQueryOptions NVARCHAR (MAX) = N'  WITH STATS = 5, NOUNLOAD '
      , @MoveFilesLocation   NVARCHAR (MAX) = N'<<MoveLocation>>'
      , @BackupFilesCount    INT            = '<<BackupCount>>'
      , @Credential          NVARCHAR (300);

SET @Credential = CONCAT('https://', @StorageAccount, '.blob.core.windows.net/', @Container);
DROP TABLE IF EXISTS #Output
                   , #MoveStatements;

DECLARE @CredentialVerification NVARCHAR (MAX);
IF EXISTS (
              SELECT    1
                  FROM  sys.credentials
                  WHERE name = @Credential
          )
    BEGIN
        SET @CredentialVerification = N'{
		"cell_type": "markdown",
		"source": ["# Credential: ' + @Credential + N' Exists\r\n",
					"- Credential exists but secret key has not been validated. You can visit your Storage Account on Azure to retrieve the secret key. "],
		"metadata": {
			"azdata_cell_guid": ""' + LOWER(NEWID()) + N'",
			"tags": []
		},';
    END;
ELSE
    BEGIN
        SET @CredentialVerification
            = N'{
		"cell_type": "markdown",
		"source": ["# Credential: ' + @Credential
              + N' cannot be found. \r\n",
					"- The specified credential cannot be found. \n",
					"- Use the script below to generate the specified credential\n",
					"- Visit you azure portal to retrieve the secret key for the storage accouunt specified"],
		"metadata": {
			"azdata_cell_guid": "' + LOWER(NEWID()) + N'",
			"tags": []
		}
		},{
            "cell_type": "code",
            "source": [
                "CREATE CREDENTIAL [' + @Credential + N']\n",
                "WITH IDENTITY = ''SHARED ACCESS SIGNATURE'',SECRET =''Your SECRET KEY goes here''\n"],
			"metadata": {
			"azdata_cell_guid": "' + LOWER(NEWID()) + N'",
			"tags": []
			},
            "outputs": [],
            "execution_count": 1
        },';
    END;



SET NOCOUNT ON;
CREATE TABLE #Output
(
    DatabaseName  VARCHAR (100)
  , BackupScript  VARCHAR (MAX)
  , RestoreScript VARCHAR (MAX)
);

SELECT  @RestoreQueryOptions = IIF(@Replace = 1, CONCAT(@RestoreQueryOptions, ',REPLACE '), @RestoreQueryOptions);

DECLARE @BackupQueryStart  NVARCHAR (MAX) = N''
      , @BackupFiles       NVARCHAR (MAX) = N''
      , @RestoreQueryStart NVARCHAR (MAX) = N''
      , @Date              DATE           = CAST(GETDATE() AS DATE)
      , @Database          VARCHAR (100)
      , @Url               VARCHAR (1000) = ''  -- Always leave blank
      , @MoveStatement     VARCHAR (MAX);

CREATE TABLE #MoveStatements
(
    DB            sysname
  , MoveStatement VARCHAR (MAX)
);

EXEC sp_MSforeachdb '
	INSERT INTO #MoveStatements
	    (
	        DB
	      , MoveStatement
	    )
    SELECT ''?'' as DB,(
	SELECT '', '' + ''MOVE N''''''+name+'''''' TO N''''''+RIGHT(physical_name,CHARINDEX(''\'',Reverse(Replace(physical_name,''/'',''\'')))-1)+'''''''' FROM ?.sys.database_files df
	FOR XML PATH('''')) as MoveStatement  ';


UPDATE  #MoveStatements
    SET MoveStatement = REPLACE(MoveStatement, 'TO N''', 'TO N''' + @MoveFilesLocation + '\');

DECLARE BackupAndRestoreCursor CURSOR FORWARD_ONLY FOR(
    SELECT      [name]
        FROM    sys.databases
        WHERE   database_id > 4);
OPEN BackupAndRestoreCursor;
FETCH NEXT FROM BackupAndRestoreCursor
INTO @Database;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @MoveStatement = '';
        SET @MoveStatement = (
                                 SELECT     MoveStatement
                                     FROM   #MoveStatements AS MS
                                     WHERE  MS.DB = @Database
                             );
        SET @BackupQueryStart = N'"BACKUP DATABASE ' + @Database + N' TO\n", ';
        SET @RestoreQueryStart = N'"RESTORE DATABASE ' + @Database + N' FROM\n", ';
        IF @BackupFilesCount = 1
            BEGIN
                SET @Url = 'https://' + @StorageAccount + '.blob.core.windows.net/' + @Container + '/' + @Database + '_backup_' + CAST(@Date AS VARCHAR (20)) + '.bak';
                SET @BackupFiles = N'"URL = ''' + @Url + N'''\n",';
            END;
        IF @BackupFilesCount > 1
            BEGIN
                DECLARE @QueryLoopCount INT          = 1
                      , @LineEnding     NVARCHAR (4) = N'';

                WHILE @QueryLoopCount <= @BackupFilesCount
                    BEGIN
                        IF @QueryLoopCount < @BackupFilesCount
                            SET @LineEnding = N',';
                        ELSE
                            SET @LineEnding = N'';

                        SET @Url = 'https://' + @StorageAccount + '.blob.core.windows.net/' + @Container + '/' + @Database + '_backup_' + CAST(@Date AS VARCHAR (20)) + '_' + CAST(@QueryLoopCount AS NVARCHAR (4)) + '.bak';
                        SET @BackupFiles = @BackupFiles + N' 
						"\t\tURL = '''     + @Url + N'''' + @LineEnding + N'\n",';
                        SET @QueryLoopCount = @QueryLoopCount + 1;
                    END;
            END;



        INSERT INTO #Output
            (
                DatabaseName
              , BackupScript
              , RestoreScript
            )
        VALUES
            (
                @Database
              , @BackupQueryStart + @BackupFiles + '
			  ' + CONCAT('"\t\t', @BackupQueryOptions) + '\n",
			  ' + CONCAT('"\t\t, CREDENTIAL =''', @Credential, '''\n"')
              , @RestoreQueryStart + @BackupFiles + N'
                ' + CONCAT('"\t\t', @RestoreQueryOptions) + N'\n",
                ' + CONCAT('"\t\t', @MoveStatement) + '\n",
				' + CONCAT('"\t\t, CREDENTIAL =''', @Credential, '''\n"')
            );

        SET @BackupQueryStart = N'';
        SET @BackupFiles = N'';



        FETCH NEXT FROM BackupAndRestoreCursor
        INTO @Database;
    END;
CLOSE BackupAndRestoreCursor;
DEALLOCATE BackupAndRestoreCursor;


IF OBJECT_ID('tempdb..#MasterScript') IS NOT NULL
    DROP TABLE #MasterScript;

CREATE TABLE #MasterScript
(
    BackupScript  VARCHAR (MAX)
  , RestoreScript VARCHAR (MAX)
);


DECLARE @DatabaseHeader NVARCHAR (MAX)
      , @DatabaseName   VARCHAR (100)
      , @RestoreColumn  NVARCHAR (MAX)
      , @BackupColumn   NVARCHAR (MAX)
      , @BackupScript   NVARCHAR (MAX) = N''
      , @RestoreScript  NVARCHAR (MAX) = N'';

SET @BackupScript = N'{"cell_type": "markdown",
            "source": [
                "# DATABASE BACKUPS"
            ],
            "metadata": {
                "azdata_cell_guid": "' + LOWER(NEWID()) + N'",
                "tags": []
            }
        },';
SET @RestoreScript = N'{"cell_type": "markdown",
            "source": [
                "# DATABASE RESTORES"
            ],
            "metadata": {
                "azdata_cell_guid": "' + LOWER(NEWID()) + N'",
                "tags": []
            }
        },';

DECLARE @EndCounter TINYINT = 0
      , @DBCount    TINYINT = (
                                  SELECT    COUNT(DatabaseName)
                                      FROM  #Output AS O
                              )
      , @ArrayEnd   NVARCHAR (3);

DECLARE MasterScript CURSOR FOR(SELECT  * FROM  #Output);
OPEN MasterScript;
FETCH NEXT FROM MasterScript
INTO @DatabaseName
   , @BackupColumn
   , @RestoreColumn;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @EndCounter = @EndCounter + 1;
        IF @EndCounter < @DBCount
            BEGIN
                SET @ArrayEnd = N',';
            END;
        ELSE
            BEGIN
                SET @ArrayEnd = N'';
            END;
        SET @BackupScript = @BackupScript + N'{
				"cell_type": "markdown",
            "source": [
                "## '       + @DatabaseName + N'"
            ],
            "metadata": {
                "azdata_cell_guid": "' + LOWER(NEWID()) + N'",
                "tags": []
            }
        },
		 {
            "cell_type": "code",
            "source": ['    + @BackupColumn + N'],
            "metadata": {
                "azdata_cell_guid": "' + LOWER(NEWID()) + N'",
                "tags": []
            },
            "outputs": [],
            "execution_count": 1
        },';

        SET @RestoreScript = @RestoreScript + N'{
				"cell_type": "markdown",
            "source": [
                "## '        + @DatabaseName + N'"
            ],
            "metadata": {
                "azdata_cell_guid": "' + LOWER(NEWID()) + N'",
                "tags": []
            }
        },
		 {
            "cell_type": "code",
            "source": ['     + @RestoreColumn + N'],
            "metadata": {
                "azdata_cell_guid": "' + LOWER(NEWID()) + N'",
                "tags": []
            },
            "outputs": [],
            "execution_count": 1
        }'                   + @ArrayEnd;


        FETCH NEXT FROM MasterScript
        INTO @DatabaseName
           , @BackupColumn
           , @RestoreColumn;
    END;
CLOSE MasterScript;
DEALLOCATE MasterScript;
INSERT INTO #MasterScript
    (
        BackupScript
      , RestoreScript
    )
VALUES
    (
        @BackupScript   -- BackupScript - varchar(max)
      , @RestoreScript  -- RestoreScript - varchar(max)
    );

SELECT      JSON_QUERY('{
						"metadata": {
							"kernelspec": {
								"name": "SQL",
								"display_name": "SQL",
								"language": "sql"
							},
							"language_info": {
								"name": "sql",
								"version": ""
							}
						},
						"nbformat_minor": 5,
						"nbformat": 4,
						"cells": [
		' + @CredentialVerification + BackupScript + RestoreScript + '
								]
								}')
    FROM    #MasterScript;