How to backup Azure SQL Server Databases (.bacpac) to Google Cloud SQL

Create a new file called index.js and using npm install the following dependencies:express,@google-cloud/storage, googleapis and google-auth-library. Then paste the following code:

                                
const express = require('express');
const { readFileSync } = require('fs');
const handlebars = require('handlebars');
const { Storage } = require('@google-cloud/storage');
const { google } = require('googleapis');

const app = express();

app.get('/', async (req, res) => {

  //if the GET parameter backup=true exists
  if (req.query.backup != 'true') {
    return res.status(200).send('backup not requested');
  }
  //download bacpac file from google storage
  const storage = new Storage({
    keyFilename: 'YOUR_JSON_KEY_FILE',
  });
  const bucket = storage.bucket('YOUR_BUCKET_NAME');
  //list .bacpac files in bucket and download the most recent one
  const [files] = await bucket.getFiles();
  if (files.length == 0) return;
  files.sort((a, b) => {
    //conver to utc time
    const aTime = new Date(a.metadata.timeCreated).getTime();
    const bTime = new Date(b.metadata.timeCreated).getTime();
    return bTime - aTime;
  });
  await files.at(0).download({ destination: files.at(0).name });
  //load google apis using a json key file
  const auth = new google.auth.GoogleAuth({
    keyFile: 'YOUR_JSON_KEY_FILE',
    scopes: 'https://www.googleapis.com/auth/sqlservice.admin',
  });

  //load auth to google options
  const options = {
    auth: auth,
  };
  google.options(options);
  const client = await auth.getClient();

  //patch the sql instance to change the setting activationPolicy to ALWAYS
  const sql_patch = await google.sqladmin('v1beta4').instances.patch({
    auth: client,
    project: 'YOUR_PROJECT_NAME',
    instance: 'YOUR_SQL_INSTANCE_NAME',
    requestBody: {
      settings: {
        activationPolicy: 'ALWAYS',
      },
    },
  });

  //fetch the operation status
  let sql_operation_status = { data: { status: '' } };
  do {
    sql_operation_status = await google.sqladmin('v1beta4').operations.get({
      auth: client,
      project: 'YOUR_PROJECT_NAME',
      operation: sql_patch.data.name,
    });
    await new Promise(resolve => setTimeout(resolve,30000));
  } while (sql_operation_status.data.status != 'DONE')

  //fetch ip from aws GET request
  const my_ip = await fetch('http://checkip.amazonaws.com/');
  let ip = await my_ip.text();
  //trim new line
  ip = ip.trim();
  //add ip to google cloud sql whitelist

  const sql_result = await google.sqladmin('v1beta4').instances.patch({
    auth: client,
    project: 'YOUR_PROJECT_NAME',
    instance: 'YOUR_SQL_INSTANCE_NAME',
    requestBody: {
      settings: {
        ipConfiguration: {
          authorizedNetworks: [
            {
              name: 'present-ip',
              value: ip,
            },
          ],
        },
      },
    },
  });
        //fetch the operation status
  sql_operation_status = { data: { status: '' } };
  do {
    sql_operation_status = await google.sqladmin('v1beta4').operations.get({
      auth: client,
      project: 'YOUR_PROJECT_NAME',
      operation: sql_result.data.name,
    });
    //wait for 30 seconds
    await new Promise(resolve => setTimeout(resolve, 30000));
  } while (sql_operation_status.data.status != 'DONE')

        //await new Promise(resolve => setTimeout(resolve, 15000));
  //delete a database belonging to the google sql instance
  const sql_delete = await google.sqladmin('v1beta4').databases.delete({
    auth: client,
    project: 'YOUR_PROJECT_NAME',
    instance: 'YOUR_SQL_INSTANCE_NAME',
    database: 'YOUR_DATABASE_NAME',
  });

        //fetch the operation status
  sql_operation_status = { data: { status: '' } };
  do {
    sql_operation_status = await google.sqladmin('v1beta4').operations.get({
      auth: client,
      project: 'YOUR_PROJECT_NAME',
      operation: sql_delete.data.name,
    });
    //wait for 30 seconds
    await new Promise(resolve => setTimeout(resolve, 30000));
  } while (sql_operation_status.data.status != 'DONE')

console.log("Database deleted going to create a new one");

  //create a new database belonging to the google sql instance
  const sql_create = await google.sqladmin('v1beta4').databases.insert({
    auth: client,
    project: 'YOUR_PROJECT_NAME',
    instance: 'YOUR_SQL_INSTANCE_NAME',
    requestBody: {
      name: 'YOUR_DATABASE_NAME',
    },
  });
console.log("Database created going to import");
        await new Promise(resolve => setTimeout(resolve, 15000));
  //run sqlpackage
  const { exec } = require('child_process');
  exec(`sqlpackage/sqlpackage /a:Import /tsn:YOUR_SQL_SERVER_IP /tdn:YOUR_SQL_INSTANCE_NAME /tu:sqlserver /tp:YOUR_SQL_PASSWORD /sf:${files.at(0).name} /TargetTrustServerCertificate:True`, (err, stdout, stderr) => {
    if (err) {
      //some err occurred
      console.error(err)
    } else {
            exec(`rm ${files.at(0).name}`);
      // the *entire* stdout and stderr (buffered)
      console.log("Database imported, powering it off");
      //patch the sql instance to change the setting activationPolicy to ALWAYS
      google.sqladmin('v1beta4').instances.patch({
        auth: client,
        project: 'YOUR_PROJECT_NAME',
        instance: 'YOUR_SQL_INSTANCE_NAME',
        requestBody: {
          settings: {
            activationPolicy: 'NEVER',
          },
        },
      });
    }
  });
  return;
});

const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(
    `Hello from Cloud Run! The container started successfully and is listening for HTTP requests on ${PORT}`
  );
});