Skip to content

Instantly share code, notes, and snippets.

@charlesbedrosian

charlesbedrosian/RestoreFromAzure.bat

Created November 15, 2019 07:16
Show Gist options
  • Save charlesbedrosian/603863b8c04755e32e01b47ee8ff345d to your computer and use it in GitHub Desktop.
Save charlesbedrosian/603863b8c04755e32e01b47ee8ff345d to your computer and use it in GitHub Desktop.
Download ZIP
Copying data-tier applications between environments
Raw
RestoreFromAzure.bat
@echo off
REM ***************************************************************************
REM To use:
REM provide your source and destination server configuration parameters.
REM It will append a date to the restored database catalog because the database
REM should not exist when restoring.
REM
REM This is very useful for pulling a copy of an Azure-hosted SQL Server database
REM to your local instance for development purposes.
REM
REM Requires https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-ver15
REM
REM ***************************************************************************
REM ***************************************************************************
REM Create the date and time elements.
REM ***************************************************************************
for /f "tokens=1-7 delims=:/-, " %%i in ('echo exit^|cmd /q /k"prompt $d $t"') do (
for /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^|date') do (
set dow=%%i
set %%a=%%j
set %%b=%%k
set %%c=%%l
set hh=%%m
set min=%%n
set ss=%%o
)
)
REM ***************************************************************************
REM *** Set Source config
REM ***************************************************************************
SET source_host=
SET source_catalog=
SET source_username=
SET source_password=
REM ***************************************************************************
REM ***************************************************************************
REM *** Set Destination config
REM ***************************************************************************
REM SET destination_host=.\sqlexpress
REM SET destination_catalog=
REM SET loca_username=
REM SET destination_password=
SET destination_host=
SET destination_catalog=%source_catalog%-%yy%-%mm%-%dd%
SET destination_username=
SET destination_password=
REM ***************************************************************************
SET bacpac_file="%USERPROFILE%\Downloads\%source_catalog%.bacpac"
SET sqlpackage="C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe"
SET source_cs="Data Source=%source_host%;Initial Catalog=%source_catalog%;User ID=%source_username%;Password=%source_password%"
SET destination_cs="Data Source=%destination_host%;Initial Catalog=%destination_catalog%;User ID=%destination_username%;Password=%destination_password%"
echo ***************************************************************************
echo *** Exporting data-tier application
echo ***************************************************************************
%sqlpackage% /a:Export /d /tf:%bacpac_file% /scs:%source_cs%
echo ***************************************************************************
echo *** Importing data-tier application to %destination_catalog%
echo ***************************************************************************
%sqlpackage% /a:Import /sf:%bacpac_file% /tcs:%destination_cs%
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment